1、上次分享了《Excel怎样批量提取文件夹和子文件夹所有文件》，将演示该文件另存为文件名《Excel如何把文件夹下所有文件表合并汇总文件内 .xlsm》（演示文件，下面代码复制到能运行宏的工作簿都可以）如图。
2、然后按下快捷键ALT+F11打开VBE（宏）编辑界面，在模块1【列取所有文件名嘉滤淀驺】后面复制如下代码：Sub 所有文件表汇总() &#泌驾台佐39;2020-10-21 21:33:47 Dim sh As Worksheet, r As Long, myr As Range, myrs As Range, m As String Dim shmm As String, d As Object, wbm As Workbook, wb As Workbook r = Range("C" & Rows.Count).End(xlUp).Row If r = 4 Then Exit Sub m = Range("B1").Text Set myrs = Range("C5:C" & r) Workbooks.Add Set wbm = ActiveWorkbook On Error Resume Next Set d = CreateObject("scripting.dictionary") For Each myr In myrs If myr.Text <> "" Then Workbooks.Open myr.Text Set wb = ActiveWorkbook For Each sh In ActiveWorkbook.Sheets If sh.Range("J2").Text <> "" Then shmm = sh.Range("J2").Text Else shmm = sh.Range("H2").Text End Ifmy: d.Add shmm, "" If Err.Number <> 0 Then If IsNumeric(Right(shmm, 1)) Then shmm = Left(shmm, Len(shmm) - 1) & Val(Right(shmm, 1)) + 1 Else shmm = shmm & "1" End If Err.Clear GoTo my End If sh.Name = shmm sh.Copy after:=wbm.Sheets(wbm.Sheets.Count) Next sh wb.Close SaveChanges:=False End If Next myr ActiveWorkbook.SaveAs Filename:=m & "\" & "总表.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=FalseEnd Sub
3、以上操作动态过程如下：
4、回到工作表窗口，首先在B1填写文件夹路径，然后点下【列举所有文件名】命令按钮，列出所有文件，然后运行【所有文件表汇总】宏（菜单栏中点【视图】中下列表中【宏】列表【查看宏(V)】打开宏对方框，选该宏名，执行），生成“总表”工作簿文件并汇总上面文件所有表，运行过程如下图。
5、如果觉得这篇经验帮到了您，请点击下方的 “投票点赞" 或者“收藏”支持我！还有疑问的话可以点击下方的 “我有疑问”，谢谢啦！