vba 合并多个excel的不同工作表到同一个excel

您可以使用VBA编写一个宏来合并多个Excel文件的不同工作表到同一个Excel文件中。以下是一个示例代码：

Sub MergeWorksheets()
Dim path As String
Dim currentWB As Workbook, targetWB As Workbook
Dim currentWS As Worksheet, targetWS As Worksheet
Dim currentRow As Long, lastRow As Long

'选择目标工作簿
Set targetWB = Application.Workbooks.Open("C:\TargetWorkbook.xlsx")

'选择源工作簿所在文件夹
path = Application.GetOpenFilename(filefilter:="Excel Files (*.xls*),*.xls*", Title:="Select Files to Merge", MultiSelect:=True)

'循环遍历每个源工作簿中的每个工作表
For i = LBound(path) To UBound(path)
    Set currentWB = Application.Workbooks.Open(path(i))
    For Each currentWS In currentWB.Worksheets
        '在目标工作簿中添加新工作表
        Set targetWS = targetWB.Worksheets.Add(After:=targetWB.Worksheets(targetWB.Worksheets.Count))
        '将当前工作表的数据复制到目标工作簿的新工作表中
        currentWS.UsedRange.Copy targetWS.Range("A1")
        '调整目标工作表的格式
        With targetWS
            .Cells.EntireColumn.AutoFit
            lastRow = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            .ListObjects.Add(xlSrcRange, Range("A1:D" & lastRow), , xlYes).Name = "Table1"
            .ListObjects("Table1").TableStyle = "TableStyleMedium2"
        End With
    Next currentWS
    '关闭源工作簿
    currentWB.Close False
Next i

'保存目标工作簿
targetWB.Save
'关闭目标工作簿
targetWB.Close
End Sub

在运行此宏之前，请确保将目标工作簿的文件路径更改为您要将工作表合并到的实际文件路径。您还可以根据需要进行其他格式更改。