写一段vba代码合并多个excel工作簿按相同表头合并为一个工作表

以下是一个示例代码，可以将多个Excel工作簿中的数据按照相同的表头合并到一个工作表中：

Sub MergeWorkbooks() Dim Path As String Dim Filename As String Dim Sheet As Worksheet Dim CopyRange As Range Dim LastRow As Long Dim LastColumn As Long Dim TargetSheet As Worksheet Dim SourceWorkbook As Workbook Dim TargetWorkbook As Workbook

'设置目标工作簿和目标工作表
Set TargetWorkbook = ThisWorkbook
Set TargetSheet = TargetWorkbook.Sheets("MergedData")

'设置文件路径
Path = "C:\Users\username\Desktop\"

'列出要合并的文件名
Filename = Dir(Path & "*.xlsx")

'遍历所有文件
Do While Filename <> ""
    '打开工作簿
    Set SourceWorkbook = Workbooks.Open(Path & Filename)

    '循环遍历所有工作表
    For Each Sheet In SourceWorkbook.Worksheets
        '找到表头所在行
        Set CopyRange = Sheet.Rows(1)

        '找到最后一行和最后一列
        LastRow = TargetSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastColumn = TargetSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

        '将数据复制到目标工作表中
        CopyRange.Copy
        TargetSheet.Cells(LastRow + 1, 1).PasteSpecial xlPasteValuesAndNumberFormats
        TargetSheet.Cells(LastRow + 1, 1).PasteSpecial xlPasteFormats
        TargetSheet.Cells(LastRow + 1, 1).PasteSpecial xlPasteColumnWidths

        '清除剪贴板
        Application.CutCopyMode = False
    Next Sheet

    '关闭工作簿
    SourceWorkbook.Close False

    '列出下一个文件名
    Filename = Dir
Loop

'自动调整列宽
TargetSheet.Columns.AutoFit

End Sub

请注意，这段代码假定所有工作簿中的表头都在第一行，并且所有工作簿中的表头都相同。如果工作簿中的表头不同，或者表头不在第一行，需要相应地修改代码。