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:=xlOpenXMLWorkbook '将工作簿另存为EXCEL默认格式
ActiveWorkbook.Close
Next
MsgBox "文件已经被分拆完毕!"
End Sub
按运行按钮或F5直接运行,程序会自动为你拆分工作簿并不会影响原工作簿。并在当前工作簿所在目录下生成所有工作表的工作簿。
拆分xls工作簿
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