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