请求编写Excel宏：将多工作表数据合并至Master工作表

Hey there, I’ve put together a simple, robust VBA macro that’ll fix your data consolidation problem. It loops through every sheet in your workbook, grabs all valid data (excluding headers since your Master sheet should already have matching ones), and appends it to the Master sheet without overwriting existing content.

Here’s the Code #

Sub ConsolidateToMaster()
    Dim ws As Worksheet
    Dim masterWs As Worksheet
    Dim lastRow As Long
    Dim masterLastRow As Long
    
    ' Set reference to the Master sheet (update name if yours is different)
    On Error Resume Next
    Set masterWs = ThisWorkbook.Worksheets("Master")
    On Error GoTo 0
    
    ' Check if Master sheet exists first
    If masterWs Is Nothing Then
        MsgBox "Master sheet not found! Please create a sheet named 'Master' before running this macro.", vbExclamation
        Exit Sub
    End If
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Skip the Master sheet to avoid copying its own data
        If ws.Name <> "Master" Then
            ' Find the last row with data in the current sheet (column A as anchor)
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            ' Only copy if there's data beyond the header row
            If lastRow > 1 Then
                ' Find the next empty row in Master sheet to paste new data
                masterLastRow = masterWs.Cells(masterWs.Rows.Count, "A").End(xlUp).Row + 1
                
                ' Copy data from row 2 to lastRow, columns A to AB
                ws.Range("A2:AB" & lastRow).Copy Destination:=masterWs.Range("A" & masterLastRow)
            End If
        End If
    Next ws
    
    MsgBox "Data consolidation finished successfully!", vbInformation
End Sub

Key Details About the Macro #

• Skips Master Sheet: The loop intentionally ignores the Master sheet so you don’t end up duplicating its data.
• Finds Valid Data Automatically: Uses End(xlUp) to locate the last row with content in each sheet—no need to hardcode row numbers.
• Safe Appending: It always finds the next empty row in the Master sheet before pasting, so existing data won’t get overwritten.
• Error Prevention: Checks if the Master sheet exists first, so you get a clear message if it’s missing instead of a confusing error.

Steps to Use #

• Open your target Excel workbook.
• Press Alt + F11 to open the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > Module.
• Paste the code above into the new module.
• Press F5 to run the macro, or go back to Excel and launch it via the Developer tab > Macros.

Quick Tips #

• Make sure your Master sheet already has the same header row (columns A to AB) as your other sheets—this macro only copies data rows, not headers.
• If your headers are in a row other than row 1, just change the 2 in ws.Range("A2:AB" & lastRow) to match your first data row.
• Always back up your workbook before running macros, just to play it safe!

内容的提问来源于stack exchange，提问作者BlankB