多工作簿表格合并：跳过表头、添加来源标识及单元格定位问题

Let's break down solutions to each of your issues, with a fully revised code that addresses all three problems:

1. Preserve First Source File's Header, Skip Subsequent Headers #

To handle this, we'll use a boolean flag (isFirstFile) to track whether we're processing the first source file. For the first file, we copy the entire range including the header row; for every file after that, we start copying from row 2 to skip the header.

2. Add a Source Filename Column #

After pasting each batch of data into the master sheet, we'll identify the rows that were just added and fill an empty column (e.g., column O) with the current source file's name. This lets you trace each row back to its original workbook.

3. Replace Hardcoded Row Number with Dynamic Calculation #

Instead of using a random large number like A12785, we'll calculate the last used row in the master sheet dynamically. This ensures we always paste data into the first empty row, regardless of how much data is already present.

Revised Full Code #

Option Explicit
Sub Merge()
    Dim SrPath As String
    Dim MaPath As String
    Dim SrName As String
    Dim MaName As String
    Dim SrTemplate As String
    Dim MaTemplate As String
    Dim SrWS As Worksheet
    Dim MaWS As Worksheet
    Dim isFirstFile As Boolean ' Flag to track first source file
    Dim lastRow As Long ' Last row with data in master sheet
    Dim pasteStartRow As Long, pasteEndRow As Long ' Ranges for pasted data
    
    'Define folders and filenames
    SrPath = "C:\Users\Documents\Test\"
    MaPath = "C:\Users\Documents\Test\Master\"
    SrTemplate = "*.xlsx"
    MaTemplate = "Master.xlsm" ' Uncommented to specify master file template
    
    'Open the master file and get the summary worksheet
    MaName = Dir(MaPath & MaTemplate)
    If MaName = "" Then
        MsgBox "Master file not found in specified path!", vbExclamation
        Exit Sub
    End If
    Workbooks.Open MaPath & MaName
    Set MaWS = ActiveWorkbook.Worksheets("Summary")
    
    'Initialize flag for first file
    isFirstFile = True
    
    'Open each source file, copying the data into the master sheet
    SrName = Dir(SrPath & SrTemplate) 'Retrieve the first file
    Do While SrName <> ""
        'Open the source file and get the data worksheet
        Workbooks.Open SrPath & SrName
        Set SrWS = ActiveWorkbook.Worksheets("data")
        
        'Copy data: include header for first file, skip header for others
        Dim sourceLastRow As Long
        sourceLastRow = SrWS.Cells(SrWS.Rows.Count, "A").End(xlUp).Row
        
        If isFirstFile Then
            SrWS.Range("A1:N" & sourceLastRow).Copy
        Else
            ' Skip header if source has data beyond row 1
            If sourceLastRow >= 2 Then
                SrWS.Range("A2:N" & sourceLastRow).Copy
            End If
        End If
        
        'Find the next empty row in master sheet
        lastRow = MaWS.Cells(MaWS.Rows.Count, "A").End(xlUp).Row
        If lastRow = 1 And MaWS.Range("A1").Value = "" Then
            ' Master sheet is empty, paste starting at A1
            MaWS.Range("A1").PasteSpecial Paste:=xlPasteValues
            pasteStartRow = 1
        Else
            ' Paste starting at next row after last used
            MaWS.Range("A" & lastRow + 1).PasteSpecial Paste:=xlPasteValues
            pasteStartRow = lastRow + 1
        End If
        
        'Get the end row of the pasted data
        pasteEndRow = MaWS.Cells(MaWS.Rows.Count, "A").End(xlUp).Row
        
        'Add source filename to column O (adjust column letter as needed)
        MaWS.Range("O" & pasteStartRow & ":O" & pasteEndRow).Value = SrName
        
        'Close the current source file and get the next
        Workbooks(SrName).Close SaveChanges:=False
        
        'Update flag after first file
        If isFirstFile Then
            isFirstFile = False
        End If
        
        'Get next source file
        SrName = Dir
    Loop
    
    'Save and close the master file
    Workbooks(MaName).Close SaveChanges:=True
    MsgBox "Merge completed successfully!", vbInformation
End Sub

Key Changes Explained #

• Header Handling: The isFirstFile flag controls whether we copy from row 1 or row 2. After the first iteration, the flag is set to False to skip all future headers.
• Source Filename Column: We calculate the start and end rows of the pasted data, then fill column O with SrName (the current source file's name). Adjust the column letter (e.g., change "O" to "P") if needed.
• Dynamic Row Calculation: MaWS.Cells(MaWS.Rows.Count, "A").End(xlUp).Row finds the last row with data in column A of the master sheet. We paste starting at the next row, so there's no need for hardcoded or random row numbers.
• Bug Fixes: Fixed variable name typos (e.g., sumWS → MaWS), uncommented the master file template definition, added error handling for missing master files, and added a completion message.

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