Excel多Sheet拆分与合并

文章目录

Excel多Sheet拆分与合并一、Excel多个Sheet拆分二、多个Excel合并成一个Excel（每个Sheet则是一个原Excel）

一、Excel多个Sheet拆分

1.打开Excel，鼠标右击sheet栏，【查看代码】

2.将如下代码复制进去，并执行

Private Sub 分拆工作表()Dim sht As WorksheetDim MyBook As WorkbookSet MyBook = ActiveWorkbookFor Each sht In MyBook.Sheetssht.CopyActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name, FileFormat:=xlWorkbookDefault '将工作簿另存为EXCEL默认格式ActiveWorkbook.CloseNextMsgBox "文件已经被分拆完毕!"End Sub

3.选择存放目录等

二、多个Excel合并成一个Excel（每个Sheet则是一个原Excel）

1.打开Excel，鼠标右击sheet栏，【查看代码】

2.将如下代码复制进去，并执行

Sub Workbook_merge()Rem This script is used to collect worksheets of serval workbooks into one workbook!Dim FileOpenDim X As IntegerDim Wb As WorkbookDim sh As WorksheetApplication.ScreenUpdating = FalseFileOpen = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbook(*.xlsx),*.xlsx", MultiSelect:=True, Title:="Please select the Workbooks you want to merge:")X = 1Application.DisplayAlerts = FalseWhile X <= UBound(FileOpen)Set Wb = GetObject(FileOpen(X))For Each sh In Wb.SheetsIf Application.WorksheetFunction.CountA(sh.Cells) <> 0 Thensh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)End IfNextWb.Close SaveChanges:=FalseX = X + 1WendApplication.DisplayAlerts = FalseThisWorkbook.SaveApplication.ScreenUpdating = TrueEnd Sub

3.一次可选择多个Excel