批量合并Excel

正好有人来问如何将excel多个簿中多个表合成一个簿多个表（虽然网上已经有很多类似的代码了），还是在这里分享一下吧。

Sub 合并工作簿()

Dim sourceWorkbook As Workbook

Dim FileName As String

Dim sht As Worksheet

FileName = Dir(ThisWorkbook.Path & "\*")

Do While FileName ""

If FileName ThisWorkbook.Name Then

Set sourceWorkbook = Workbooks.Open(ThisWorkbook.Path & "\" & FileName)

For Each sht In sourceWorkbook.Sheets

sht.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Next sht

sourceWorkbook.Close SaveChanges:=False

End If

FileName = Dir

Loop

End Sub

以上代码的作用是将同一文件夹下面的Excel里的Sheet都合并到当前工作簿里。

有问题欢迎留言私戳～