How to Consolidate Multiple Excel Workbooks Using VBA

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.

Associated: Superior Microsoft Excel Options You Should Know


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

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.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


Home windows(MyFile).Activate

'shut the open workbook as soon as the information is pasted


'empty the cache to retailer the worth of the subsequent workbook

MyFile = Dir()

'open the subsequent file within the folder


'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.

Associated: Web sites and Blogs to Study Excel Ideas and Methods

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.

Excel VBA code snippet

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.

Associated: Finest On-line Programs to Grasp Superior Excel

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.

Excel VBA code snippet

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") & ""


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.

Person coding on a laptop
5 Cool Microsoft Excel Macros for Sorting Information

Make information administration a breeze with these Excel macros.

Learn Subsequent

About The Writer

Leave a Comment