1.先把所有要合并的EXCEL放到同一文件夹里,
2.在当前文件夹里新建一个EXCEL,
3.打开新建的EXCEL,在工作表标签sheet上点击右键,选择“查看代码”,
4.在打开的VBA编辑窗口中粘贴以下代码:
5.点击“运行”,选择“运行子过程/用户窗体”就OK了。
Sub Macro1()
Dim MyPath$, MyName$, sh As Worksheet, sht As Worksheet, m&
Set sh = ActiveSheet
MyPath = ThisWorkbook.Path & "\"
MyName = Dir(MyPath & "*.xls")
Application.ScreenUpdating = False
Cells.ClearContents
Do While MyName <> ""
If MyName <> ThisWorkbook.Name Then
With GetObject(MyPath & MyName)
For Each sht In .Sheets
If IsSheetEmpty = IsEmpty(sht.UsedRange) Then
m = m + 1
If m = 1 Then
sht.[a1].CurrentRegion.Copy sh.[a1]
Else
sht.[a1].CurrentRegion.Offset(1).Copy sh.[a65536].End(xlUp).Offset(1)
End If
End If
Next
.Close False
End With
End If
MyName = Dir
Loop
Application.ScreenUpdating = True
End Sub
本文来源:https://www.2haoxitong.net/k/doc/762d2d4624c52cc58bd63186bceb19e8b8f6ecd1.html
文档为doc格式