文章目錄
-
- 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. 測試
- 運作之前:
- 運作宏之後: