这个问题描述不是很清楚,比如每个工作簿有多个表格要汇总,还是只汇总其中一个表格。是否都是第一个表格?等等。
给一段以前的代码供参考:
Sub 合并文件()
Application.DisplayAlerts = False
Dim mYfile, ar(), n%, i%
Dim myPath As String
Dim myBook
Dim mySheet
Dim W1, W2 As String
Dim S1 As String
W1 = ActiveWorkbook.Name
myPath = ThisWorkbook.Path
EXCELFILE = Dir(myPath & "\*.xlsx")
Do Until Len(EXCELFILE) = 0
n = n + 1
ReDim Preserve ar(1 To n)
ar(n) = EXCELFILE
EXCELFILE = Dir
Loop
For i = 1 To n
If ar(i) <> W1 Then
Set myBook = Workbooks.Open(myPath & "\" & ar(i))
W2 = ActiveWorkbook.Name
For Each mySheet In myBook.Sheets
S1 = ar(i) & "-" & mySheet.Name
mySheet.Select
ActiveSheet.Cells.Select
Selection.Copy
Windows(W1).Activate
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = S1
ActiveSheet.Paste
Windows(W2).Activate
Next mySheet
ActiveWorkbook.Close
End If
Next i
Application.DisplayAlerts = True
End Sub