When working with varied data sources, you might often struggle to compile multiple workbooks and worksheets before arriving at one final data piece. Imagine a situation where you have a few hundred workbooks to combine before you can even begin your day.
No one wants to spend endless hours working on different sources, opening each workbook, copying and pasting the data from various sheets, before finally making one consolidated workbook. What if a VBA macro can do this for you?
With this guide, you can create your own Excel VBA macro code to consolidate multiple workbooks, all in a matter of minutes (if the data files are a lot).
Pre-Requisites for Creating Your Own VBA Macro Code
You need one workbook to house the VBA code, while the rest of the source data workbooks are separate. Additionally, create one workbook Consolidated to store the consolidated data from all your workbooks.
Create a folder Consolidation at your preferred location to store all your source workbooks. When the macro runs, it would toggle through each workbook stored within this folder, copy the contents from various sheets, and place it in the Consolidated workbook.
Creating Your Own Excel VBA Code
Once the pre-requisites are out of the way, it is time to delve into the code and start hacking away at the basics to adapt it to your requirements.
Press the Alt+F11 key on Excel to open the VBA macro code editor. Paste the code written below and save the file as a Macro enabled workbook (.xlsm extension).
Sub openfiles()
'declare the variables used within the VBA code
Dim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long
'disable these functions to enhance code processing
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'change the path of the folder where your files are going to be saved
MyFolder = InputBox("Enter path of the Consolidation folder") & "\"
'define the reference of the folder in a macro variable
MyFile = Dir(MyFolder)
'open a loop to cycle through each individual workbook stored in the folder
Do While Len(MyFile) > 0
'activate the Consolidation workbook
Windows("Consolidation").Activate
'calculate the last populated row
Range("a1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
'open the first workbook within the Consolidation folder
Workbooks.Open Filename:=MyFolder & MyFile
Windows(MyFile).Activate
'toggle through each sheet within the workbooks to copy the data
Dim ws As Worksheet
For Each ws In Sheets
ws.Activate
ws.AutoFilterMode = False
'ignore the header and copy the data from row 2
If Cells(2, 1) = "" Then GoTo 1
GoTo 10
1: Next
10: Range("a2:az20000").Copy
Windows("Consolidation").Activate
'paste the copied contents
ActiveSheet.Paste
Windows(MyFile).Activate
'close the open workbook once the data is pasted
ActiveWorkbook.Close
'empty the cache to store the value of the next workbook
MyFile = Dir()
'open the next file in the folder
Loop
'enable the disabled functions for future use
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
The VBA Code Explained
The first part of the code is defining a subroutine, which holds all your VBA code. Define the subroutine with sub, followed by the name of the code. The sub name can be anything; ideally, you should keep a name relevant to the code you are about to write.
Excel VBA understands user-created variables and their corresponding data types declared with dim (dimension).
To enhance the processing speed of your code, you can turn off screen updating and suppress all alerts, as that slows down the code execution.
The user will be prompted for the path of the folder where the data files are stored. A loop is created to open each workbook stored within the folder, copy the data from each sheet, and append it to the Consolidation workbook.
The Consolidation workbook is activated so that Excel VBA can calculate the last populated row. The last cell within the worksheet is selected, and the last row is calculated within the workbook using the offset function. This is highly useful, when the macro starts appending data from the source files.
As the loop opens the first source file, the filters are removed from every single sheet (if they exist), and the data ranging from A2 to AZ20000 will be copied and pasted into the Consolidation workbook.
The process is repeated until all the workbook sheets are appended within the master workbook.
Finally, the source file is closed once all the data is pasted. The next workbook is opened so that the VBA macro can repeat the same steps for the next set of files.
The loop is coded to run till all the files are automatically updated in the master workbook.
User-Based Customizations
Sometimes, you don't want to worry about inbuilt prompts, especially, if you are the end-user. If you would rather hardcode the path of the Consolidation folder in the code, you can change this part of the code:
MyFolder = InputBox("Enter path of the Consolidation folder") & "\"
To:
MyFolder = “Folder path” & "\"
Additionally, you can also change the column references, as the step is not included in this code. Just replace the end column reference with your last populated column value (AZ, in this case). You need to remember that the last populated row is calculated via the macro code, so you need to change the column reference only.
To make the most out of this macro, you can use it only to consolidate workbooks in the same format. If the structures are different, you can't use this VBA macro.
Consolidating Multiple Workbooks Using Excel VBA Macro
Creating and modifying an Excel VBA code is relatively easy, especially if you understand some of the nuances within the code. VBA systematically runs through each code line and executes it line by line.
If you make any changes to the code, you must ensure you don't change the order of the codes, as that will disrupt the code's execution.