[發問] 請問不同sheet的處理-巨集要如何簡化?(已解決)

棋語鳥鳴

請問下列巨集如何簡化!分別有30個Sheets(test1~test30)做同樣的動作，下列為前2個Sheets(test1~test2)的巨集，其實裡面差別只在於sheet名稱不同而已!請問如有30個Sheets或者更多要如何簡化其巨集???
ps.以上sheet名稱都相同只有後面編號不同而已!

1. Sheets("test1").Select
   
2.     Range("A2:E2").Select
   
3.     Selection.AutoFilter
   
4.     Range("A2").Select
   
5.     ActiveWorkbook.Worksheets("test1").AutoFilter.Sort.SortFields.Clear
   
6.     ActiveWorkbook.Worksheets("test1").AutoFilter.Sort.SortFields.Add Key:= _
   
7.         Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
   
8.         xlSortNormal
   
9.     With ActiveWorkbook.Worksheets("test1").AutoFilter.Sort
   
10.         .Header = xlYes
    
11.         .MatchCase = False
    
12.         .Orientation = xlTopToBottom
    
13.         .SortMethod = xlPinYin
    
14.         .Apply
    
15.     End With
    
16.     Range("E2").Select
    
17.     ActiveWorkbook.Worksheets("test1").AutoFilter.Sort.SortFields.Clear
    
18.     ActiveWorkbook.Worksheets("test1").AutoFilter.Sort.SortFields.Add(Range("E2") _
    
19.         , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
    
20.         199, 206)
    
21.     With ActiveWorkbook.Worksheets("test1").AutoFilter.Sort
    
22.         .Header = xlYes
    
23.         .MatchCase = False
    
24.         .Orientation = xlTopToBottom
    
25.         .SortMethod = xlPinYin
    
26.         .Apply
    
27.     End With
    
28.     Selection.AutoFilter
    
29.     Range("A1").Select
    
30.     Sheets("test2").Select
    
31.     Range("A2:E2").Select
    
32.     Selection.AutoFilter
    
33.     Range("A2").Select
    
34.     ActiveWorkbook.Worksheets("test2").AutoFilter.Sort.SortFields.Clear
    
35.     ActiveWorkbook.Worksheets("test2").AutoFilter.Sort.SortFields.Add Key:= _
    
36.         Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    
37.         xlSortNormal
    
38.     With ActiveWorkbook.Worksheets("test2").AutoFilter.Sort
    
39.         .Header = xlYes
    
40.         .MatchCase = False
    
41.         .Orientation = xlTopToBottom
    
42.         .SortMethod = xlPinYin
    
43.         .Apply
    
44.     End With
    
45.     Range("E2").Select
    
46.     ActiveWorkbook.Worksheets("test2").AutoFilter.Sort.SortFields.Clear
    
47.     ActiveWorkbook.Worksheets("test2").AutoFilter.Sort.SortFields.Add(Range("E2") _
    
48.         , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
    
49.         199, 206)
    
50.     With ActiveWorkbook.Worksheets("test2").AutoFilter.Sort
    
51.         .Header = xlYes
    
52.         .MatchCase = False
    
53.         .Orientation = xlTopToBottom
    
54.         .SortMethod = xlPinYin
    
55.         .Apply
    
56.     End With

複製代碼

oobird

for i = 1 to 30'最前面加上這個循環指令
Sheets("test" & i).Select
所有
("test1")都改為("test" & i)
最後加一個next

棋語鳥鳴

回復 2# oobird
原來我之前用到Sheets(""test" & i").Select<==多用了" "難怪一直錯誤!，謝謝您的指導~讓我知道我的錯誤在哪了!
循環指令~真是個好用的東西~~