How to Combine Data from Multiple Sheets into a MasterSheet

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:

1. Press Alt + F8 in Excel.
2. Select Button1_Click.
3. 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