標題: [發問] 工作表資料分類另存工作表 [打印本頁]

---

作者: missbb    時間: 2024-9-15 15:29     標題: 工作表資料分類另存工作表

各位, 本人有一WORKBOOK內有資料, 需要在SORTING "地區" 和"組別" 後. 按"地區"另存新WORKSHEET, 並以"地區" 作為WORKSHEET名稱, 但下列CODE有問題, 求教問題所在:D
[attach]37887[/attach]

1. Sub TSsheet2()
   
2. 
   
3. Application.ScreenUpdating = 0
   
4. Dim rng As Range, d As Object, ws As Worksheet
   
5. Set ws = ActiveSheet
   
6. ws.AutoFilterMode = False
   
7. c = [a5].CurrentRegion.Columns.Count
   
8. Set d = CreateObject("scripting.dictionary")
   
9. Set rng = Range([a5], Cells(Cells(Rows.Count, 1).End(1).Row, c))
   
10. a = rng.Columns(3)
    
11. For i = 2 To UBound(a)
    
12.     d(a(i, 1)) = ""
    
13. Next
    
14. k = d.keys
    
15. For i = 0 To UBound(k)
    
16.     ws.Copy after:=Sheets(Sheets.Count)
    
17.     ActiveSheet.AutoFilterMode = False
    
18.     [a5].CurrentRegion.Clear
    
19.     With ws
    
20.         rng.AutoFilter Field:=1, Criteria1:=k(i)
    
21.         .[a5].CurrentRegion.Copy [a5]
    
22.         
    
23.     End With
    
24.         
    
25.     ActiveSheet.Name = [A6].Value
    
26. Next
    
27. Application.ScreenUpdating = 1
    
28. 
    
29. With Sheets("data").Activate
    
30.     If ActiveSheet.FilterMode Then
    
31.       ActiveSheet.ShowAllData
    
32.     End If
    
33.    
    
34. End With
    
35. 
    
36. End Sub

複製代碼

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)