If you regularly manage workbooks with dozens of daily or monthly tabs, manually copying and pasting rows into a master summary sheet is tedious and prone to human error.

In this guide, you’ll learn how to run a simple VBA macro that automatically combines data from all open worksheets into a single, clean Master Sheet with just one click.

Why Automate Sheet Consolidation?

Consolidating data across multiple tabs is a daily task in reporting, inventory management, and financial audits. Using VBA for this workflow ensures:

• Zero Manual Mistakes: Prevents missed rows or double-pasted datasets.
• Dynamic Row Detection: Works automatically regardless of how many rows each sheet contains.
• Header Protection: Copies column headers only once from the first sheet.

Step 1: Open the VBA Editor Window

1. Open your Excel workbook containing the sheets you wish to combine.
2. Press Alt + F11 (Option + F11 on Mac) to launch the Visual Basic Editor.
3. In the top menu bar, click Insert ➔ Module to open a clean code window.

Step 2: Copy and Paste the Consolidation VBA Code

Copy the code block below and paste it directly into your blank module window:

Sub CombineAllSheets()
    Dim ws As Worksheet
    Dim masterWs As Worksheet
    Dim lastRow As Long
    Dim masterLastRow As Long
    Dim isFirstSheet As Boolean
    
    isFirstSheet = True
    
    ' 1. Disable screen updating for faster execution
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' 2. Delete existing "Master" sheet if it already exists
    On Error Resume Next
    Worksheets("Master").Delete
    On Error GoTo 0
    
    ' 3. Create a new "Master" sheet at the beginning
    Set masterWs = Worksheets.Add(Before:=Worksheets(1))
    masterWs.Name = "Master"
    
    ' 4. Loop through every sheet in the workbook
    For Each ws In Worksheets
        If ws.Name <> masterWs.Name Then
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            ' Ensure sheet has data beyond row 1
            If lastRow >= 1 Then
                If isFirstSheet Then
                    ' Copy header + data from the first worksheet
                    ws.Rows("1:" & lastRow).Copy masterWs.Range("A1")
                    isFirstSheet = False
                Else
                    ' Find next empty row in Master sheet
                    masterLastRow = masterWs.Cells(masterWs.Rows.Count, "A").End(xlUp).Row + 1
                    ' Copy data only (skip header row 1)
                    ws.Rows("2:" & lastRow).Copy masterWs.Range("A" & masterLastRow)
                End If
            End If
        End If
    Next ws
    
    ' 5. Auto-fit column widths on Master Sheet
    masterWs.Columns.AutoFit
    
    ' 6. Re-enable updates & show alert
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "All sheets successfully merged into 'Master' sheet!", vbInformation, "Excel Owl Automation"
End Sub

Step 3: Run the Macro Using Alt + F8

You can execute this automation right from your Excel window at any time:

1. Switch back to your main Excel window.
2. Press Alt + F8 (or Option + F8 on Mac) to bring up the Macro dialog box.
3. Select CombineAllSheets from the list.
4. Click Run.

💡 Pro Tip: Quick Execution inside VBA Editor
If you are actively working in the code editor, place your cursor anywhere inside the script and press F5 to run it immediately.

Step 4: Save as Macro-Enabled Workbook (.xlsm)

To keep your macro active for future use, save your file in the macro-enabled format:

1. Click File ➔ Save As (or press F12).
2. In the Save as type drop-down menu, choose Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

⚠️ Important Note: Regular .xlsx files do not support macro scripts. Saving as .xlsx will erase your VBA code permanently.