合并EXCLE下所有工作簿的全部工作表

将这些文件copy到一个文件夹（只有这些Excel，且若打开某一文件，数据就能看见——即不用点其他sheet），建一新Excel，也存到该文件夹。仅打开该新Excel，按Alt+F11，左边窗口右键点，插入模块，在右边窗口粘贴如下代码：

Sub 合并当前目录下所有工作簿的全部工作表() Dim MyPath, MyName, AWbName Dim Wb As Workbook, WbN As String Dim G As Long Dim Num As Long Dim BOX As String

Application.ScreenUpdating = False MyPath = ActiveWorkbook.Path MyName = Dir(MyPath & \AWbName = ActiveWorkbook.Name Num = 0

Do While MyName <> \If MyName <> AWbName Then

Set Wb = Workbooks.Open(MyPath & \Num = Num + 1

With Workbooks(1).ActiveSheet

.Cells(.Range(\+ 2, 1) = Len(MyName) - 4) For G = 1 To Sheets.Count

Left(MyName, Wb.Sheets(G).UsedRange.Copy .Cells(.Range(\1, 1) Next

WbN = WbN & Chr(13) & Wb.Name Wb.Close False End With End If

MyName = Dir Loop

Range(\

Application.ScreenUpdating = True

MsgBox \共合并了\个工作薄下的全部工作表。如下：\& WbN, vbInformation, \提示\End Sub

Sub 合并当前工作簿下的所有工作表() Application.ScreenUpdating = False For j = 1 To Sheets.Count

If Sheets(j).Name <> ActiveSheet.Name Then X = Range(\ Sheets(j).UsedRange.Copy Cells(X, 1)

End If Next

Range(\

Application.ScreenUpdating = True

MsgBox \当前工作簿下的全部工作表已经合并完毕！\提示\End Sub

合并相同文件夹下所有表中同名sheet1、sheet2、sheet3

Sub Macro1()

Dim MyPath$, MyName$, sh As Worksheet, d As Object, r& Set d = CreateObject(\ MyPath = ThisWorkbook.Path & \ MyName = Dir(MyPath & \ Application.ScreenUpdating = False Application.DisplayAlerts = False For Each sh In Sheets

If sh.Name <> ActiveSheet.Name Then sh.Delete Next

Do While MyName <> \

If MyName <> ThisWorkbook.Name Then With GetObject(MyPath & MyName) For Each sh In .Sheets

If IsSheetEmpty = IsEmpty(sh.UsedRange) Then If Not d.Exists(sh.Name) Then sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Set d(sh.Name) = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Else

With d(sh.Name)

r = .UsedRange.Row + .UsedRange.Rows.Count + 2 sh.UsedRange.Copy .Cells(r, 1) End With

End If End If Next

.Close False End With End If

MyName = Dir Loop

Sheets(1).Activate

Application.ScreenUpdating = True End Sub

本文来源：https://www.bwwdw.com/article/jcia.html