多工作簿\表的一键合并

1、右键工作表，点查看代码

2、插入模块，在模块中，复制以下代码，单击运行

亦或增加宏按钮

Sub 合并()

   '————————————————————————————————————————
    '自定义各数据类型
    Dim FileToOpen As Variant
    Dim i, RW, CL, RW_1, CL_1 As Long
    Dim sht As Worksheet
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    '判断工作簿中是否包含《汇总表》，如过不存在就新增一个
    On Error Resume Next
    ThisWorkbook.Sheets.Add after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ActiveSheet.Name = "汇总表"
    If Err.Number > 0 Then ActiveSheet.Delete
    On Error GoTo 0
   
    '多选或单选要合并的工作薄/表，如果没有选择任何文件则退出
    FileToOpen = Application.GetOpenFilename("Excel文件,*.xl*", , "请选择要合并的多个工作簿/表", , True)
    If VBA.TypeName(FileToOpen) = "Boolean" Then
        MsgBox "没有选择文件": Exit Sub
    End If
   
    '合并过程，遍历选中的每个工作薄、工作表，在首列增加来源的工作薄+工作表名
    On Error Resume Next
    For i = 1 To UBound(FileToOpen)
         Workbooks.Open Filename:=FileToOpen(i)
         For Each sht In ActiveWorkbook.Sheets
            sht.Activate
            RW_1 = sht.UsedRange.Rows.Count
            CL_1 = sht.UsedRange.Columns.Count
            If RW_1 + CL_1 > 0 Then
                RW = ThisWorkbook.Sheets("汇总表").UsedRange.Rows.Count
                If RW > 1 Then
                    ActiveSheet.Cells(1, 1).Resize(RW_1, CL_1).Copy _
                        Destination:=ThisWorkbook.Sheets("汇总表").Cells(RW + 1, 2)
                    ThisWorkbook.Sheets("汇总表").Cells(RW + 1, 1) = "来源表"
                    ThisWorkbook.Sheets("汇总表").Cells(RW + 2, 1).Resize(RW_1 - 1, 1) = ActiveWorkbook.Name & "-" & sht.Name
                Else
                    ActiveSheet.Cells(1, 1).Resize(RW_1, CL_1).Copy _
                        Destination:=ThisWorkbook.Sheets("汇总表").Cells(1, 2)
                    ThisWorkbook.Sheets("汇总表").Cells(1, 1) = "来源表"
                    ThisWorkbook.Sheets("汇总表").Cells(2, 1).Resize(RW_1 - 1, 1) = ActiveWorkbook.Name & "-" & sht.Name
                End If
            End If
         Next sht
         ActiveWorkbook.Close
     Next i
   
    On Error GoTo 0
    MsgBox "汇总表完成"
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

3、选中需要合并的工作薄（一个或多个），之后单击“打开”

4、合并完成后的结果，出现在汇总表里

首列为合并的工作薄名+表名