- 帖子
- 216
- 主題
- 71
- 精華
- 0
- 積分
- 292
- 點名
- 0
- 作業系統
- window xp
- 軟體版本
- 2007
- 閱讀權限
- 20
- 性別
- 女
- 註冊時間
- 2012-6-27
- 最後登錄
- 2024-9-18
|
各位, 本人有一WORKBOOK內有資料, 需要在SORTING "地區" 和"組別" 後. 按"地區"另存新WORKSHEET, 並以"地區" 作為WORKSHEET名稱, 但下列CODE有問題, 求教問題所在:D
 工作表另存 (2).zip (22.65 KB)
- Sub TSsheet2()
- Application.ScreenUpdating = 0
- Dim rng As Range, d As Object, ws As Worksheet
- Set ws = ActiveSheet
- ws.AutoFilterMode = False
- c = [a5].CurrentRegion.Columns.Count
- Set d = CreateObject("scripting.dictionary")
- Set rng = Range([a5], Cells(Cells(Rows.Count, 1).End(1).Row, c))
- a = rng.Columns(3)
- For i = 2 To UBound(a)
- d(a(i, 1)) = ""
- Next
- k = d.keys
- For i = 0 To UBound(k)
- ws.Copy after:=Sheets(Sheets.Count)
- ActiveSheet.AutoFilterMode = False
- [a5].CurrentRegion.Clear
- With ws
- rng.AutoFilter Field:=1, Criteria1:=k(i)
- .[a5].CurrentRegion.Copy [a5]
-
- End With
-
- ActiveSheet.Name = [A6].Value
- Next
- Application.ScreenUpdating = 1
- With Sheets("data").Activate
- If ActiveSheet.FilterMode Then
- ActiveSheet.ShowAllData
- End If
-
- End With
- End Sub
|
|
發表於 
