[發問] 由第7張工作表起另存新檔

missbb

大大, 我寫了一段CODE, 想由活頁部內第7張工作表及之後的工作表,另存新檔, 但失敗, 可否賜教?

1. Sub sheetsavefile2()
   
2. 'xxxxxxxxxxxxxxxxxxxx
   
3. Application.ScreenUpdating = False
   
4. 
   
5. Dim a As Integer
   
6. 
   
7. For a = 7 To Sheets.Count
   
8.      
   
9. For Each sh In Worksheets
   
10.         
    
11.         sh.Copy
    
12.                
    
13.         ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sh.Name & ".xlsx"
    
14.         Workbooks(sh.Name & ".xlsx").Close True
    
15.               
    
16.     Next
    
17.    
    
18.     Next a
    
19.    
    
20. Application.ScreenUpdating = True
    
21. MsgBox ("已完成另存個別檔案")
    
22. 
    
23. End Sub

複製代碼

Hsieh

回復 1# missbb

1. Sub ex()
   
2. Application.ScreenUpdating = False
   
3. For i = 7 To Sheets.Count
   
4.    Sheets(i).Copy
   
5.   With ActiveWorkbook
   
6.      .SaveAs ThisWorkbook.Path & "\" & ActiveSheet.Name & ".xls"
   
7.      .Close
   
8.   End With
   
9. Next
   
10. MsgBox "已完成個別檔案儲存"
    
11. End Sub

複製代碼