Sub 合并目录所有工作簿全部工作表()
Dim MP, MN, AW, Wbn, wn
Dim Wb As Workbook
Dim i, a, b, d, c, e
Application.ScreenUpdating = False
MP = ActiveWorkbook.Path
MN = Dir(MP & "" & "*.xls")
AW = ActiveWorkbook.Name
Num = 0
e = 1
Do While MN <> ""
If MN <> AW Then
Set Wb = Workbooks.Open(MP & "" & MN)
a = a + 1
With Workbooks(1).ActiveSheet
For i = 1 To Sheets.Count
If Sheets(i).Range("a1") <> "" Then
Wb.Sheets(i).Range("a1").Resize(1, Sheets(i).UsedRange.Columns.Count).Copy .Cells(1, 1)
d = Wb.Sheets(i).UsedRange.Columns.Count
c = Wb.Sheets(i).UsedRange.Rows.Count - 1
wn = Wb.Sheets(i).Name
.Cells(1, d + 1) = "表名"
.Cells(e + 1, d + 1).Resize(c, 1) = MN & wn
e = e + c
Wb.Sheets(i).Range("a2").Resize(c,d).Copy .Cells(.Range("a1048576").End(xlUp).Row + 1, 1)
End If
Next
Wbn = Wbn & Chr(13) & Wb.Name
Wb.Close False
End With
End If
MN = Dir
Loop
Range("a1").Select
Application.ScreenUpdating = True
MsgBox "共合并了" & a & "个工作薄下全部工作表。如下:" & Chr(13) & Wbn, vbInformation, "提示"
End Sub
扫码关注5G通信官方公众号,免费领取以下5G精品资料
1、回复“iot6”免费领取《【8月30号登载】物联网创新技术与产业应用蓝皮书——物联网感知技术及系统应用》
2、回复“6G31”免费领取《基于云网融合的6G关键技术白皮书》
3、回复“IM6G”免费领取《6G典型场景和关键能力白皮书》
4、回复“SPN2”免费领取《中国移动SPN2.0技术白皮书》
5、回复“LTKJ7”免费领取《 联通科技周17本白皮书合集》
6、回复“5g-a”免费领取《中国联通5G-A 通感算融合技术白皮书》
7、回复“URLLC”免费领取《中国联通5G URLLC 技术白皮书》
8、回复“LDSL”免费领取《中国移动算力网络技术白皮书》