Consolidating data from multiple sheets into a single sheet can be essential for analysis and reporting. Instead of manually copying and pasting data from each sheet, a VBA macro can automate the process, saving time and minimizing errors.
This post will guide you through setting up a VBA macro to combine data from all sheets in your Excel, workbook into a “MasterSheet,” excluding any specific sheets you wish to omit.
Introduction to the VBA Macro
The VBA macro presented here will efficiently transfer data from all sheets (except “MasterSheet”) into a single “MasterSheet.” This automation ensures that data is consolidated without manual intervention, improving both accuracy and speed.
Breaking Down the Code (Line-by-Line)
Option Explicit
This line enforces the declaration of all variables before use. This practice helps prevent potential errors, such as typos in variable names.
Private Sub Button1_Click()
This line starts the macro that will be executed when the button is clicked. It triggers the data consolidation process.
Dim totalsheets As Integer
Dim lastrow As Long
Dim i As Integer
Dim j As Long
Here, variables are declared:
totalsheets
: Stores the total number of worksheets in the workbook.lastrow
: Determines the last row with data in the current worksheet.i
: Used as a counter for iterating through each worksheet.j
: Used as a counter for iterating through each row of data.
totalsheets = Worksheets.Count
This line assigns the total number of worksheets in the workbook to the totalsheets
variable. Worksheets.Count
provides the count of all sheets.
For i = 1 To totalsheets
This line initiates a loop that iterates through each worksheet. The variable i
represents the index of the current worksheet.
If Worksheets(i).Name <> "MasterSheet" Then
The macro checks if the current worksheet is not named “MasterSheet.” This ensures that data from “MasterSheet” is not processed.
lastrow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
Here, lastrow
is set to the last row with data in the current worksheet. This determines where to copy data from.
For j = 2 To lastrow
This starts an inner loop that iterates through each row of data, beginning from row 2 (assuming row 1 contains headers).
Worksheets(i).Activate
Worksheets(i).Rows(j).Select
Selection.Copy
This macro activates the current worksheet, selects the row to be copied, and performs the copy action.
Worksheets("MasterSheet").Activate
The macro switches to the “MasterSheet” where the copied data will be pasted.
lastrow = Worksheets("MasterSheet").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("MasterSheet").Cells(lastrow + 1, 1).Select
ActiveSheet.Paste
The macro updates lastrow
to find the last row with data in “MasterSheet,” selects the next empty row, and pastes the copied data.
Next j
This line completes the inner loop, moving to the next row of data in the current worksheet.
Next i
End Sub
The Next i
line completes the outer loop, moving to the next worksheet. The End Sub
statement marks the end of the macro.
Full Code Example
Here is the complete VBA code you can use:
Option Explicit
Private Sub Button1_Click()
Dim totalsheets As Integer
Dim lastrow As Long
Dim i As Integer
Dim j As Long
totalsheets = Worksheets.Count
For i = 1 To totalsheets
If Worksheets(i).Name <> "MasterSheet" Then
lastrow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To lastrow
Worksheets(i).Activate
Worksheets(i).Rows(j).Select
Selection.Copy
Worksheets("MasterSheet").Activate
lastrow = Worksheets("MasterSheet").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("MasterSheet").Cells(lastrow + 1, 1).Select
ActiveSheet.Paste
Next j
End If
Next i
End Sub
Running the Code
To execute the macro:
- Press
Alt + F8
in Excel. - Select
Button1_Click
. - Click
Run
.
Alternatively, assign this macro to a button on your worksheet. Clicking the button will trigger the macro and consolidate the data.
Flow chart of the code
Result
Before
After
Upon running the macro, all data from each sheet (excluding “MasterSheet”) will be copied to “MasterSheet.” Each row from the source sheets will appear sequentially in “MasterSheet.”
Conclusion
By using this VBA macro, data consolidation across multiple sheets becomes a streamlined process. Automating this task not only saves time but also reduces errors associated with manual copying and pasting. The ability to quickly combine data into one master sheet facilitates more efficient data analysis and reporting.
Resources
For more information about using VBA in Excel, visit Microsoft’s official VBA documentation. and Pillow
if you like this blog post on How to Combine Data from Multiple Sheets into a MasterSheet, then you would love our other blog post on How to Copy Excel Files Between Folders Using VBA in Excel