When working with various information sources, you would possibly usually battle to compile a number of workbooks and worksheets earlier than arriving at one ultimate information piece. Think about a scenario the place you’ve got a couple of hundred workbooks to mix earlier than you’ll be able to even start your day.
Nobody desires to spend countless hours engaged on completely different sources, opening every workbook, copying and pasting the information from varied sheets, earlier than lastly making one consolidated workbook. What if a VBA macro can do that for you?
With this information, you’ll be able to create your individual Excel VBA macro code to consolidate a number of workbooks, all in a matter of minutes (if the information information are so much).
Pre-Requisites for Creating Your Personal VBA Macro Code
You want one workbook to deal with the VBA code, whereas the remainder of the supply information workbooks are separate. Moreover, create one workbook Consolidated to retailer the consolidated information from all of your workbooks.
Create a folder Consolidation at your most well-liked location to retailer all of your supply workbooks. When the macro runs, it might toggle via every workbook saved inside this folder, copy the contents from varied sheets, and place it within the Consolidated workbook.
Creating Your Personal Excel VBA Code
As soon as the pre-requisites are out of the way in which, it’s time to delve into the code and begin hacking away on the fundamentals to adapt it to your necessities.
Press the Alt+F11 key on Excel to open the VBA macro code editor. Paste the code written under and save the file as a Macro enabled workbook (.xlsm extension).
Sub openfiles()'declare the variables used throughout the VBA code
Dim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Lengthy
'disable these features to reinforce code processing
With Utility
.DisplayAlerts = False
.ScreenUpdating = False
Finish With
'change the trail of the folder the place your information are going to be saved
MyFolder = InputBox("Enter path of the Consolidation folder") & ""
'outline the reference of the folder in a macro variable
MyFile = Dir(MyFolder)
'open a loop to cycle via every particular person workbook saved within the folder
Do Whereas Len(MyFile) > 0
'activate the Consolidation workbook
Home windows("Consolidation").Activate
'calculate the final populated row
Vary("a1048576").Choose
Choice.Finish(xlUp).Choose
ActiveCell.Offset(1, 0).Choose
'open the primary workbook throughout the Consolidation folder
Workbooks.Open Filename:=MyFolder & MyFile
Home windows(MyFile).Activate
'toggle via every sheet throughout the workbooks to repeat the information
Dim ws As Worksheet
For Every ws In Sheets
ws.Activate
ws.AutoFilterMode = False
'ignore the header and duplicate the information from row 2
If Cells(2, 1) = "" Then GoTo 1
GoTo 10
1: Subsequent
10: Vary("a2:az20000").Copy
Home windows("Consolidation").Activate
'paste the copied contents
ActiveSheet.Paste
Home windows(MyFile).Activate
'shut the open workbook as soon as the information is pasted
ActiveWorkbook.Shut
'empty the cache to retailer the worth of the subsequent workbook
MyFile = Dir()
'open the subsequent file within the folder
Loop
'allow the disabled features for future use
With Utility
.DisplayAlerts = True
.ScreenUpdating = True
Finish With
Finish Sub
The VBA Code Defined
The primary a part of the code is defining a subroutine, which holds all of your VBA code. Outline the subroutine with sub, adopted by the identify of the code. The sub identify may be something; ideally, it is best to hold a reputation related to the code you’re about to jot down.
Excel VBA understands user-created variables and their corresponding information sorts declared with dim (dimension).
To boost the processing pace of your code, you’ll be able to flip off display updating and suppress all alerts, as that slows down the code execution.
The consumer shall be prompted for the trail of the folder the place the information information are saved. A loop is created to open every workbook saved throughout the folder, copy the information from every sheet, and append it to the Consolidation workbook.
The Consolidation workbook is activated in order that Excel VBA can calculate the final populated row. The final cell throughout the worksheet is chosen, and the final row is calculated throughout the workbook utilizing the offset operate. That is extremely helpful, when the macro begins appending information from the supply information.
Because the loop opens the primary supply file, the filters are faraway from each single sheet (in the event that they exist), and the information starting from A2 to AZ20000 shall be copied and pasted into the Consolidation workbook.
The method is repeated till all of the workbook sheets are appended throughout the grasp workbook.
Lastly, the supply file is closed as soon as all the information is pasted. The subsequent workbook is opened in order that the VBA macro can repeat the identical steps for the subsequent set of information.
The loop is coded to run until all of the information are routinely up to date within the grasp workbook.
Consumer-Primarily based Customizations
Typically, you do not wish to fear about inbuilt prompts, particularly, in case you are the end-user. If you happen to would relatively hardcode the trail of the Consolidation folder within the code, you’ll be able to change this a part of the code:
MyFolder = InputBox("Enter path of the Consolidation folder") & ""To:
MyFolder = “Folder path” & ""Moreover, you too can change the column references, because the step is just not included on this code. Simply exchange the tip column reference along with your final populated column worth (AZ, on this case). It’s essential do not forget that the final populated row is calculated through the macro code, so you might want to change the column reference solely.
To take advantage of out of this macro, you need to use it solely to consolidate workbooks in the identical format. If the buildings are completely different, you’ll be able to’t use this VBA macro.
Consolidating A number of Workbooks Utilizing Excel VBA Macro
Creating and modifying an Excel VBA code is comparatively straightforward, particularly if you happen to perceive a few of the nuances throughout the code. VBA systematically runs via every code line and executes it line by line.
If you happen to make any adjustments to the code, you could guarantee you do not change the order of the codes, as that may disrupt the code’s execution.
Learn Subsequent
About The Writer
