Using VBA to Consolidate Data from Multiple Sheets

Why do you Need to Consolidate Data?

Use VBA to consolidate data from multiple sheets into one spreadsheet. This is a step-by-step guide on how I achieved this.

I conducted the final day of a 3 day VBA training course and on this day we I took the opportunity to answer a particluar problem of the client. (Which incidently was SMC Pneumatics based in Milton Keynes).

The Consolidate Data Problem

Their question was: "How can we, using VBA, consolidate data from multiple spreadsheets?". When I quizzed them about their requirements for their VBA procedure it involved the following:

• The user would click on a form button which would open a browse dialog box.
• They would then select a folder in which there would be excel and a number of other files.
• Then, after having selected the folder, VBA would loop through and select and open only the .xlsx|.xls|.xlsm files. (The .xls file would be for Excel workbooks that were created in older versions).
• The script would then select a range in file and copy and paste it back to the main file.

So we worked on this problem during lunchtime and although this might not be the most efficienty way of accomplishing this, especially with thousands of documents, it certainly beats having to do it manually.

Writing the VBA Consolidation Code

The openMyFiles() function

Two functions, I used, to solve this problem. Well actually one function and one sub-procedure. The openMyFiles() function was designed to loop through the various Excel files in the chosen folder, the other was to simply get the folder that was chosen. Before you start, you will want to download the exercise files. The contain 4 sample data files, Which are the ones you should use for this exericse.

1. Open a New Excel file and save the file as as consolidate_reports.xlsm.
   (Remember to save it as a macro enabled workbook. Otherwise the VBA code won't run).
2. Display the Developer Tab. (File - Options - Customise Ribbon and check the Developer Tab).
3. On the Developer Tab click the Visual Basic Icon on the left to open up the Visual Basic Editor
4. Ensure that you can see the Project Explorer window on the left. (View - Project Explorer) If you can't.
5. In the Project Explorer right click on VBAProject(consolidate_reports.xlsm) and choose Insert - Module. (Modules are where you will place the VBA code for this lesson. Modules are also created when you use the Macro record button.)
   
   
6. Click at the top of the new blank module and type: Option Explicit.
   (This line makes it easier to track your variables should things go wrong. Don't know what I'm talking about? Then read further).
7. Now it's time to start the Sub Procedure. Type:
   
   Sub openMyFiles()
   
   
   
   End Sub
   
   
8. We will type the VBA code needed within the Sub/End Sub.
   ( All new code will be emboldened, so you know which bit of code you need to add.)
   
9. Next we need to Dimension or declared the variables that are going to hold the folder name, file name and file extension.Type the following bold text:
   
   Sub openMyFiles()
   Dim Source As String
   Dim strFile As String
   Dim fileExt As String
   
   End
   
10. Now to add the code to remove any unwanted pop ups that come with copying and pasting code.
    Sub openMyFiles()
    Dim Source As String
    Dim strFile As String
    Dim fileExt As String
    Application.DisplayAlerts = False
    End Sub
11. On the next line, we'll add the line that will call the GetFolder() function that we've yet to create. The result of the function is placed into the Source variable we created earlier.
    Source = GetFolder() & "\"
    
12. Next, we'll use the Dir() method to get all of the files in the selected folder. (Remember the Source variable will hold the folder selected by the user). That value is then placed into the strFile variable.
    strFile = Dir(Source)
    
13. So now it's time to build our looping statement. Basically, I'll need to loop through all of the files in the selected folder opening only the Excel files. Type the following to start the Do Loop.
    
    Do While Len(strFile) > 0
    
    Loop
    
14. Within the Do loop place set the variable fileExt to the value of the file extension of the currently looped file.
    
    Do While Len(strFile) > 0
    fileExt = Right(strFile, Len(strFile) - InStr(strFile, "."))
    Loop
15. Now we need to use an IF statement so that the only files opened are Excel files. Add the following:
    (Note the underscore allows you to continue your code on a separate line, ensure you type a space before the "line continuation" underscore.)
    Do While Len(strFile) > 0
    fileExt = Right(strFile, Len(strFile) - InStr(strFile, "."))
    If fileExt = "xlsx" Or fileExt = "xls" Or fileExt = "xlsm" _
    And strFile <>"consolidate_reports.xlsm" Then
    End If
    Loop
    
16. On the next line we'll open the workbook.Using the Source and strFile variables we set earlier. Also just before we'll add an error handler just incase the user clicks the cancel button.
    Do While Len(strFile) > 0
    fileExt = Right(strFile, Len(strFile) - InStr(strFile, "."))
    If fileExt = "xlsx" Or fileExt = "xls" Or fileExt = "xlsm" _
    Or strFile <>"consolidate_reports.xlsm" Then
    On Error GoTo ErrHandler:
    Workbooks.Open Filename:=Source & strFile
    End If
    Loop
    
17. Just to be sure we'll active the worksheet.
    Worksheets(1).Activate
    
18. Now let's select the current region.
    Range("A1").CurrentRegion.Select
    
19. Then Copy it.
    Selection.Copy
    
20. Then we'll switch back to the consolidate_reporsts.xlsm workbook.
    Workbooks("consolidate_reports.xlsm").Activate
    
21. Next we need to check if this is the first piece of information being copied. If it is we'll place the data in Range("A2") if not we will place the data underneath any other data we copied.
    
    If Range("A1").Offset(1).Value <> "" Then
    Range("A1").End(xlDown).Offset(1).Select
    Else
    Range("A2").Select
    End If
    
22. Great! Now we've selected the correct location let's paste the data. Type the following after End If
    ActiveSheet.Paste
    
23. Now we'll close the workbook we just copied the data from.
    Workbooks(strFile).Close
    
24. Now outside of the If statement but inside the loop we'll move to the next file in the folder. We'll also exit the Sub which is the result of the error handler. Bascially, if someone clicks on the Cancel button the procedure doesn't continue. I've included the entire text for the openMyFiles Sub Procedure you'll need to add the bold text.
    Sub openMyFiles()
    Dim Source As String
    Dim strFile As String
    Dim fileExt As String
    Application.DisplayAlerts = False
    Source = GetFolder() & "\"
    strFile = Dir(Source)
    Do While Len(strFile) > 0
    fileExt = Right(strFile, Len(strFile) - InStr(strFile, "."))
    If fileExt = "xlsx" Or fileExt = "xls" Or fileExt = "xlsm" _
    Or strFile <> "consolidate_reports.xlsm" Then
    Workbooks.Open Filename:=Source & strFile
    Worksheets(1).Activate
    Range("A1").CurrentRegion.Select
    Selection.Copy
    Workbooks("consolidate_reports.xlsm").Activate
    If Range("A1").Offset(1).Value <> "" Then
    Range("A1").End(xlDown).Offset(1).Select
    Else Range("A2").Select
    End If
    ActiveSheet.Paste
    Workbooks(strFile).Close
    End If
    strFile = Dir()
    Loop
    ErrHandler:
    Exit Sub
    End Sub
25. Now we need to create the GetFolder() function so the Source variable above knows what folder it's looking at; type:
    Function GetFolder() As String
    
    End Function
    
26. Inside the function well Dimension our variables.
    Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    End Function
    
27. Now we can set the value of fldr with the folderpicker.
    Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    End Function
    
28. Now we can set the properties of the msoFileDialogFolderPIcker dialog box.The If statement with the next code is to catch the return value of the Dialog box when the user clicks OK.
    Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
    .Title = "Select a folder"
    .AllowMultiSelect = False
    .InitialFileName = Application.DefaultFilePath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
    End With
    NextCode:
    GetFolder = sItem
    Set fldr = Nothing
    End Function
    
29. Nearly there! Now we will attach the openMyFiles() sub procedure to the quick access toolbar. Switch back to Excel.
30. Click on the Quick Access Toolbar drop down menu and click more commands.
    
    
31. From the Customize Quick Access toolbar menu choose For consolidation_report.xlsm.
    
32. From the Choose Commands from menu select Macros.
    
33. In the macro list click on the openMyFiles macros then click on the Add button.
34. Click Modify at the bottom. (This will allow you to change the button icon).
35. Choose an icon for the openMyFiles sub procedure.
    
36. Click OK.
37. Click OK again.
38. Now you can click on your brand new button on the quick access toolbar. Select the folder with all the Excel files in and Bob's your uncle! All of the ranges are copied into the consolidate_reports.xlsm sheet.

Now if you want to see the finished file then you're welcome to download it. Also if you would like to get a comprehensive understanding of VBA then book yourself a 3 Day VBA training course.