批量将多个excel中的多个工作簿合并到一个excel中
批量将多个excel中的多个工作簿合并到一个excel中将要合并的excel放到一个文件夹中,在这个目录中新建一个excel。
打开新建的excel。
按alt+F11。
单击【插入】菜单,【模块】。
将下面的东东复制进去,关闭对话框。
选中刚才插入的模块。
点击【运行】菜单,【运行宏】。
在弹出的对话框中选中【Combinefiles】,单击【运行】
搞定。
Sub CombineFiles()
Dim path As String
Dim FileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String
Dim MyDir As String
MyDir = ThisWorkbook.path & "\"
'ChDrive Left(MyDir, 1) 'find all the excel files
'ChDir MyDir
'Match = Dir$("")
ThisWB = https://www.360docs.net/doc/ff15194169.html,
Application.EnableEvents = False
Application.ScreenUpdating = False
path = MyDir
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then
Else
WS.Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Set Wkb = Nothing
Set LastCell = Nothing
End Sub