Excel VBA is an integral part of Excel automation, and VBA's usage and benefits can't be undermined. If you're in an uphill battle trying to consolidate multiple sheets and workbooks in Excel, we're here to help.
The macros mentioned in this guide will help you achieve the seemingly insurmountable task in a matter of seconds (or minutes, if the data assets are large).
By following this tutorial, you'll create your own VBA macro in Excel and efficiently merge multiple sheets into one single sheet.
Merging Multiple Excel Sheets in the Same Workbook
For this task, the data is stored in the following sheets:
- Sheet1
- Sheet2
- Sheet3
The sheet names listed above are for illustration purposes only. This VBA macro is generic and doesn’t depend on the sheet names; you can customize the code to use it with any sheet name(s).
Pre-Requisites for Running the Code
There are some prerequisites for running the VBA code listed below.
You need to store the macro code in a new Excel file. Save this workbook with a .xlsm extension. You can save the VBA macro workbook with any name.
Open a new Excel file; press Alt + F11 on your keyboard to open the Excel VBA editor. Once the editor opens, add a new code module by clicking on the Insert tab at the top. Select Module to insert a new module; this is where you'll be entering the VBA macro code given below.
The data sheets to be consolidated should be in another separate workbook altogether. The name of the workbook and sheets can be whatever you choose.
As soon as you execute the VBA code, the VBA macro will cycle through each available worksheet in the primary workbook (data workbook) and paste the contents into a newly added sheet within the same workbook.
The consolidated data will be available in the sheet named Consolidated.
Running the VBA Code
It’s time to run the newly saved macro code. Copy-paste this code into the VBA editor's module:
Sub consolidate_shts()
'declare the various variables used within the code and the vba data types
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Integer, lastrow1 As Integer
'disable screen flickering and alert pop-ups during the execution
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'store the name of the primary workbook in the a macro variable. Replace Test.xlsx with the name of your primary workbook
Set wbk1 = Workbooks("Test.xlsx")
'activate the workbook before performing the function(s) on it
wbk1.Activate
'run a vba for loop to check if a sheet Consolidated already exists. If it exists, the for loop will delete it.
For Each sht In wbk1.Sheets
If sht.Name = "Consolidated" Then sht.Delete
Next sht
'Add a new sheet to store the newly consolidated data
Worksheets.Add.Name = "Consolidated"
'Add some headers to each individual column within the consolidated sheet
With Sheets("Consolidated")
.Range("a1").Value = "OrderDate"
.Range("b1").Value = "Region"
.Range("c1").Value = "Rep"
.Range("d1").Value = "Item"
.Range("e1").Value = "Units"
.Range("f1").Value = "UnitCost"
.Range("g1").Value = "Total"
End With
'The newly created sheet consolidated will hold the consolidated data from each individual sheet in the primary workbook
For i = 1 To wbk1.Worksheets.Count
If Sheets(i).Name <> "Consolidated" Then
'Capture the last populated row from the data sheets in the workbook
lastrow = Sheets(i).Range("a1").End(xlDown).Row
'Capture the last populated row in the Consolidated sheet
lastrow1 = wbk1.Sheets("Consolidated").Range("a1048576").End(xlUp).Row + 1
'Copy data from source sheet and paste it in the consolidated sheet
Sheets(i).Range("a2:g" & lastrow).Copy Destination:=Sheets("Consolidated").Range("a" & lastrow1)
End If
Next i
'Enable Excel VBA functions for future use
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
The VBA Code Explained
First, declare all the variables you're using within the code and assign them with the correct VBA data types to make the code run seamlessly.
Once you declare the variables, some basic housekeeping is needed. This is done by disabling screen flickering and suppressing pop-up alerts. For example, when you delete an existing sheet using the VBA code, a prompt within Excel asks for confirmation before deleting the sheet. Prompts like this are suppressed to enhance the speed of execution.
In the next step, you need to define the workbook's name, which contains all of your data. Replace Test.xlsx with the name and extension of your workbook name. Make sure you surround the name with quotes.
Activate the primary workbook and delete any existing sheets with the name Consolidated to eliminate any previously stored data. The VBA code toggles through each sheet, and as soon as it encounters the sheet name Consolidated it'll delete it. This is done using the VBA IF statement, which checks for logical conditions and deletes the sheet as soon as the condition is met.
A new sheet is added to the primary workbook to store the consolidated data. Subsequently, pre-formatted, standardized headers are added to this sheet. You can change the values of the titles (column headers) by updating the information next to the cell references within quotes.
For example: .Range(“a1”) = “OrderDate” can be replaced with .Range(“a1”) = “OrderNumber”
Next, a VBA FOR loop toggles through each worksheet, copies the sheet’s contents, and pastes the contents into the Consolidated worksheet before moving to the next sheet in the workbook. This process repeats until all sheets are copied over.
During this process, all the rows are auto-calculated and pasted in the Consolidated sheet. The last populated row is auto-calculated before the data is pasted in. The macro is dynamic and can adjust to varying data rows within each worksheet.
Once data from all sheets is pasted into the main consolidation sheet, the macro moves to the final leg of the code. The VBA functions initially disabled are enabled again for future use.
Consolidating Multiple Sheets Using Excel VBA Macro
Excel VBA is a superfluous programming language, which works well with all Excel components. Each piece of code is essential, and it’s important to remember that the execution is dependent on a line-by-line execution system, so you shouldn't change the order of the code lines.
To customize the code for your requirements, you can make the required changes and run this code to consolidate the data efficiently and effectively in seconds.