1、新建Excel文件，“另存为”：Excel启用宏的工作簿；
2、把需要合并表放置同一个文件夹中；在B2中填写文件夹路径钱砀渝测（必填）；在B3填汇总后文件名（不填默认为“00_数据汇总”）。
3、快捷键“Alt+F11”，打开VB编辑器，“插入”-“模块”；
4、在新模块编辑栏中输入以下代码，如果同一工作簿中几个工作表都要合并时启用，代码中有此文字的行把最前面的'去掉，保留后面的'。Sub 数据汇总()Dim jIf Cells(3, 2) = "" Then j = "00_数据汇总"Else j = Cells(3, 2)End IfIf Cells(2, 2) = "" Then MsgBox "请在B2单元格填写文件夹地址" Exit SubElse GoTo TEnd IfT: pth = Cells(2, 2) & "\" fn = Dir(pth & "*.xlsx") Set newbk = Workbooks.Add Set sht = newbk.Sheets(1) k = 1 Application.DisplayAlerts = False Do While fn <> "" Set Wb = Workbooks.Open(pth & fn) ' For i = 1 To Wb.Sheets.Count'同一工作簿中几个工作表都要合并时启用 sht.Cells(k, 1) = fn & ":" & Wb.Sheets(1).Name Columns("A:E").Select Selection.EntireColumn.Hidden = False '取消隐藏 mc = Application.CountA(Sheets(1).Columns(5)) Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range(Cells(2, 1), Cells(mc, 1)).Select Selection = Left(fn, InStrRev(fn, ".") - 1) k = k + 1 Selection = fn Wb.Sheets(1).Rows("2:" & mc).Copy sht.Cells(k, 1).PasteSpecial xlPasteValuesAndNumberFormats k = sht.UsedRange.Rows.Count + 1 ' Next'同一工作簿中几个工作表都要合并时启用 Wb.Close False fn = Dir Loop newbk.SaveAs pth & j & ".xlsx" newbk.Close False Application.DisplayAlerts = TrueEnd Sub
5、按F5，开始自动合并到一个新的文件中。