如何把多个工作簿里多个工作表合并为一个工作簿的多个表

第一步：将需要合并的excel文件存放在同一目录中；
第二步：创建一个带宏的EXCEL文件，文件名设为TOOLS.XLSM;
第三步：点击开发工具-插入模块；
第四步：在新的模块中编制宏代码，代码如下：
Sub 合并文件()
Application.DisplayAlerts = False
Dim mYfile, ar(), n%, i%
Dim myPath As String
Dim myBook
Dim mySheet
Dim W1, W2 As String
Dim S1 As String
W1 = ActiveWorkbook.Name
myPath = ThisWorkbook.Path
EXCELFILE = Dir(myPath & "\*.xlsx")
Do Until Len(EXCELFILE) = 0
n = n + 1
ReDim Preserve ar(1 To n)
ar(n) = EXCELFILE
EXCELFILE = Dir
Loop
For i = 1 To n

If ar(i) <> W1 Then
Set myBook = Workbooks.Open(myPath & "\" & ar(i))
W2 = ActiveWorkbook.Name
For Each mySheet In myBook.Sheets
S1 = ar(i) & "-" & mySheet.Name
mySheet.Select
ActiveSheet.Cells.Select
Selection.Copy
Windows(W1).Activate
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = S1
ActiveSheet.Paste
Windows(W2).Activate
Next mySheet
ActiveWorkbook.Close
End If
Next i
Application.DisplayAlerts = True
End Sub

第五步：执行宏。结果如图：

如何将一张工作表拆分成多个工作表？