|
楼主 |
发表于 2019-6-29 08:47
|
显示全部楼层
这是在论坛上找到,请问如何加上“簿名”“表名”,谢谢。
Sub 合并同一文件夹下多个工作簿汇总到一个工作表中()
Application.ScreenUpdating = False
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Set sh = ThisWorkbook.Worksheets(1)
f = Dir(ThisWorkbook.Path & "\*.xls*") '生成查找EXCEL的目录,可以适应不同版本
Do While f <> "" '在目录中循环
If f <> ThisWorkbook.Name Then '如果不是打开的工作簿
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f) '依次打开目录工作薄
n = n + 1
If n = 1 Then
arr = wb.Worksheets(1).[b5].CurrentRegion
Else
arr = wb.Worksheets(1).[b5].CurrentRegion.Offset(1)
End If
Sheet1.Range("a4:z65536") = Clear
'sh.Cells(Rows.Count, 3).End(xlUp).Offset(1).Resize(UBound(arr), UBound(arr, 2)) = arr
sh.Cells(Rows.Count, 3).End(xlUp).Offset(1).Resize(UBound(arr), UBound(arr, 2)) = arr
wb.Close False '关闭打开的工作薄
End If
f = Dir
Loop '结束循环
Application.ScreenUpdating = True
MsgBox "共汇总了" & n & "个文件"
End Sub |
|