这里使用的 Office
版本:Microsoft Excel 2019
1. 右键点击 Sheet 页,选择“查看代码”
2. 粘入下面的代码
-
Mac 系统上:
Sub 工作薄拆分() Dim PATH As String PATH = Application.ActiveWorkbook.PATH Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In Sheets sht.Copy '下面这句话用来拼接生成路径 ActiveWorkbook.SaveAs PATH + "/" + sht.Name + ".xlsx" ActiveWorkbook.Close Next Application.ScreenUpdating = True End Sub
-
Win 系统上(区别主要是生成路径的区别,
Win
中目录分隔是 “\
”,Mac
中目录分隔是 “/
”):Sub 工作薄拆分() Dim PATH As String PATH = Application.ActiveWorkbook.PATH Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In Sheets sht.Copy '下面这句话用来拼接生成路径 ActiveWorkbook.SaveAs PATH + "\" + sht.Name + ".xlsx" ActiveWorkbook.Close Next Application.ScreenUpdating = True End Sub
3. 在项目中点击 VBAProject,然后运行宏
如果运行宏报错
如果在运行时报错,可以尝试如下设置:
4. 测试
-
运行之前:
-
运行宏之后: