合并多个工作簿为一个工作簿
1、右键查看代码,插入以下代码
Sub 工作薄间工作表合并()
Dim FileOpen
Dim X As Integer
Application.ScreenUpdating = False
FileOpen = Application.GetOpenFilename(FileFilter:="Microsoft Excel文件(*.xls),*.xls", MultiSelect:=True, Title:="合并工作薄")
X = 1
While X <= UBound(FileOpen)
Workbooks.Open Filename:=FileOpen(X)
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
X = X + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
errhadler:
MsgBox Err.Description
End Sub
2、保存,表格存为启用宏的工作簿,如果为xlsx记得将上面的.xls修改
3、开发工具运行宏
拆分工作簿为多个工作簿1、点击菜单插入模块,输入以下代码:
Private Sub 分拆工作表()
Dim sht As Worksheet
Dim MyBook As Workbook
Set MyBook = ActiveWorkbook
For Each sht In MyBook.Sheets
sht.Copy
ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name, FileFormat:=xlNormal '将工作簿另存为EXCEL默认格式
ActiveWorkbook.Close
Next
MsgBox "文件已经被分拆完毕!"
End Sub
2、单击运行运行子过程|窗体,拆分的文件与源文件在同地址。