文章目录
-
- 1. 右键点击 Sheet 页,选择“查看代码”
- 2. 粘入下面的代码
- 3. 在项目中点击 VBAProject,然后运行宏
-
- 如果运行宏报错
- 4. 测试
这里使用的
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. 测试
- 运行之前:
- 运行宏之后: