EXCEL应用，多个工作薄和多个工作表的合并

新建一个新的工作薄(book)

右键单击工作薄中的sheet 1标签, 选择“查找代码”，弹出Visual Basic代码编辑窗口，复制粘贴以下代码

Sub Workbook_merge()
Rem This script is used to collect worksheets of serval workbooks into one workbook!
Dim FileOpen
Dim X As Integer
Dim Wb As Workbook
Dim sh As Worksheet
Application.ScreenUpdating = False
FileOpen = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbook(*.xlsx),*.xlsx", MultiSelect:=True, Title:="Please select the Workbooks you want to merge:")
X = 1
Application.DisplayAlerts = False
While X <= UBound(FileOpen)
Set Wb = GetObject(FileOpen(X))
For Each sh In Wb.Sheets
If Application.WorksheetFunction.CountA(sh.Cells) <> 0 Then
sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next
Wb.Close SaveChanges:=False
X = X + 1
Wend
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.ScreenUpdating = True
End Sub


同一个工作薄多个sheets合并到新建的sheet当中。

代码如下

Sub Sheet_merge()
Rem This Script can be used to merge all worksheets into current worksheet!
Application.ScreenUpdating = False
For j = 1 To Sheets.Count
If Sheets(j).Name <> ActiveSheet.Name Then
X = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
Sheets(j).UsedRange.Copy ActiveSheet.Cells(X, 1)
End If
Next
Application.ScreenUpdating = True
MsgBox "All sheets have been merged!", vbInformation, "Attention"
End Sub


参考知乎