mashimaro.chu
New Member
- Joined
- Apr 23, 2008
- Messages
- 2
Hi,
I have a question regarding automating excel sorting in Excel 2007.
Here's what I have, I have a sheet that I need to sort on a weekly basis as the data is always being updated.
Here is what i am basically doing with sheet and I am using a MACRO RECORDER to automate it.
1) I select column2 ~ column 7 (B:G)
2) I sort with the following order.
- column 3 (column C) ascending order, then
- column 4 (column d) ascending order, then
- column 6 (column f) ascending order, then
- column 2 (column b) DESCENDING order.
That is pretty much it.
everything from this point on, it's repetitive.
1) I select column 12 ~ column 17 (L:Q) <<10 columns offset from earlier>>
2) I sort with the following order.
- column 13 (column m) ascending order, then
- column 14 (column n) ascending order, then
- column 16 (column p) ascending order, then
- column 12 (column l) DESCENDING order.
<<NOTICE sorts first my of OFFSET columns 10 just it?s>>
then it'll repeat and it'll be
1) I select column 22 ~ column 27 (V:AA)
2) I sort with the following order.
- column 23 (column W) ascending order, then
- column 24 (column X) ascending order, then
- column 26 (column Z) ascending order, then
- column 22 (column V) DESCENDING order.
... and it just keept going on until column 2992 ~ 2997
Here's what what I am doing so far, I am using a macro recorder and I have been recording what I described above REPETIVELY until column 242~247(IH:IM) <<RECORDING for far so times 25 repeatedly> and as you can see, it's FAR AWAY FROM column 2992:2997 <300 times>
I don't have very extensive knowledge on macro, but i was thinking if I could record what i did once (column 2~7) and do a loop (For z = 1 to 300) and repeat it. However, after examining the code, i realized the macro recorder records the SPECIFIC columns (ie. A:G), so i can't really do what i'm thinking to do.
can you please help me on it?
thanks.
===================================================================================================
The Macro below isn't really important. It is just a reference if you're need a better understanding of what I am talking about.
thanks =)
===================================================================================================
Sub A235_D1()
'
' A235_D1 Macro
'
'
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"C1:C1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"D1:D1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"F1:F1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"B1:B1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("B1:G1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("L:Q").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"M1:M1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"N1:N1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"P1:P1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"L1:L1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("L1:Q1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("V:AA").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"W1:W1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"X1:X1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"Z1:Z1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"V1:V1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("V1:AA1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("AF:AK").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AG1:AG1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AH1:AH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AJ1:AJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AF1:AF1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("AF1:AK1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("AP:AU").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AQ1:AQ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AR1:AR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AT1:AT1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AP1:AP1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("AP1:AU1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("AZ:BE").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BA1:BA1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BB1:BB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BD1:BD1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AZ1:AZ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("AZ1:BE1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("BJ:BO").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BK1:BK1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BL1:BL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BN1:BN1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BJ1:BJ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("BJ1:BO1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("BT:BY").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BU1:BU1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BV1:BV1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BX1:BX1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BT1:BT1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("BT1:BY1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("CD:CI").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CE1:CE1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CF1:CF1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CH1:CH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CD1:CD1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("CD1:CI1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("CN:CS").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CO1:CO1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CP1:CP1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CR1:CR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CN1:CN1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("CN1:CS1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("CX:DC").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CY1:CY1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CZ1:CZ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DB1:DB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CX1:CX1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("CX1:DC1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("DH:DM").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DI1:DI1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DJ1:DJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DL1:DL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DH1:DH1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("DH1:DM1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("DR:DW").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DS1:DS1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DT1:DT1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DV1:DV1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DR1:DR1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("DR1:DW1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("EB:EG").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EC1:EC1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"ED1:ED1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EF1:EF1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EB1:EB1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("EB1:EG1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("EL:EQ").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EM1:EM1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EN1:EN1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EP1:EP1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EL1:EL1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("EL1:EQ1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("EV:FA").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EW1:EW1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EX1:EX1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EZ1:EZ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EV1:EV1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("EV1:FA1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("FF:FK").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FG1:FG1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FH1:FH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FJ1:FJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FF1:FF1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("FF1:FK1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("FP:FU").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FQ1:FQ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FR1:FR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FT1:FT1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FP1:FP1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("FP1:FU1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("FZ:GE").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GA1:GA1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GB1:GB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GD1:GD1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FZ1:FZ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("FZ1:GE1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("GJ:GO").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GK1:GK1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GL1:GL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GN1:GN1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GJ1:GJ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("GJ1:GO1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("GT:GY").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GU1:GU1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GV1:GV1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GX1:GX1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GT1:GT1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("GT1:GY1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("HD:HI").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HE1:HE1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HF1:HF1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HH1:HH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HD1:HD1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("HD1:HI1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("HN:HS").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HO1:HO1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HP1:HP1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HR1:HR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HN1:HN1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("HN1:HS1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("HX:IC").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HY1:HY1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HZ1:HZ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IB1:IB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HX1:HX1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("HX1:IC1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("IH:IM").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"II1:II1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IJ1:IJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IL1:IL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IH1:IH1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("IH1:IM1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("IN:IN").Select
End Sub
I have a question regarding automating excel sorting in Excel 2007.
Here's what I have, I have a sheet that I need to sort on a weekly basis as the data is always being updated.
Here is what i am basically doing with sheet and I am using a MACRO RECORDER to automate it.
1) I select column2 ~ column 7 (B:G)
2) I sort with the following order.
- column 3 (column C) ascending order, then
- column 4 (column d) ascending order, then
- column 6 (column f) ascending order, then
- column 2 (column b) DESCENDING order.
That is pretty much it.
everything from this point on, it's repetitive.
1) I select column 12 ~ column 17 (L:Q) <<10 columns offset from earlier>>
2) I sort with the following order.
- column 13 (column m) ascending order, then
- column 14 (column n) ascending order, then
- column 16 (column p) ascending order, then
- column 12 (column l) DESCENDING order.
<<NOTICE sorts first my of OFFSET columns 10 just it?s>>
then it'll repeat and it'll be
1) I select column 22 ~ column 27 (V:AA)
2) I sort with the following order.
- column 23 (column W) ascending order, then
- column 24 (column X) ascending order, then
- column 26 (column Z) ascending order, then
- column 22 (column V) DESCENDING order.
... and it just keept going on until column 2992 ~ 2997
Here's what what I am doing so far, I am using a macro recorder and I have been recording what I described above REPETIVELY until column 242~247(IH:IM) <<RECORDING for far so times 25 repeatedly> and as you can see, it's FAR AWAY FROM column 2992:2997 <300 times>
I don't have very extensive knowledge on macro, but i was thinking if I could record what i did once (column 2~7) and do a loop (For z = 1 to 300) and repeat it. However, after examining the code, i realized the macro recorder records the SPECIFIC columns (ie. A:G), so i can't really do what i'm thinking to do.
can you please help me on it?
thanks.
===================================================================================================
The Macro below isn't really important. It is just a reference if you're need a better understanding of what I am talking about.
thanks =)
===================================================================================================
Sub A235_D1()
'
' A235_D1 Macro
'
'
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"C1:C1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"D1:D1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"F1:F1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"B1:B1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("B1:G1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("L:Q").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"M1:M1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"N1:N1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"P1:P1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"L1:L1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("L1:Q1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("V:AA").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"W1:W1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"X1:X1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"Z1:Z1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"V1:V1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("V1:AA1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("AF:AK").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AG1:AG1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AH1:AH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AJ1:AJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AF1:AF1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("AF1:AK1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("AP:AU").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AQ1:AQ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AR1:AR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AT1:AT1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AP1:AP1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("AP1:AU1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("AZ:BE").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BA1:BA1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BB1:BB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BD1:BD1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"AZ1:AZ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("AZ1:BE1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("BJ:BO").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BK1:BK1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BL1:BL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BN1:BN1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BJ1:BJ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("BJ1:BO1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("BT:BY").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BU1:BU1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BV1:BV1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BX1:BX1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"BT1:BT1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("BT1:BY1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("CD:CI").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CE1:CE1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CF1:CF1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CH1:CH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CD1:CD1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("CD1:CI1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("CN:CS").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CO1:CO1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CP1:CP1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CR1:CR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CN1:CN1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("CN1:CS1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("CX:DC").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CY1:CY1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CZ1:CZ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DB1:DB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"CX1:CX1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("CX1:DC1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("DH:DM").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DI1:DI1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DJ1:DJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DL1:DL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DH1:DH1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("DH1:DM1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("DR:DW").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DS1:DS1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DT1:DT1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DV1:DV1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"DR1:DR1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("DR1:DW1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("EB:EG").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EC1:EC1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"ED1:ED1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EF1:EF1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EB1:EB1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("EB1:EG1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("EL:EQ").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EM1:EM1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EN1:EN1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EP1:EP1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EL1:EL1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("EL1:EQ1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("EV:FA").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EW1:EW1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EX1:EX1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EZ1:EZ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"EV1:EV1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("EV1:FA1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("FF:FK").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FG1:FG1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FH1:FH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FJ1:FJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FF1:FF1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("FF1:FK1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("FP:FU").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FQ1:FQ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FR1:FR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FT1:FT1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FP1:FP1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("FP1:FU1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("FZ:GE").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GA1:GA1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GB1:GB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GD1:GD1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"FZ1:FZ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("FZ1:GE1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("GJ:GO").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GK1:GK1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GL1:GL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GN1:GN1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GJ1:GJ1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("GJ1:GO1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("GT:GY").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GU1:GU1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GV1:GV1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GX1:GX1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"GT1:GT1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("GT1:GY1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("HD:HI").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HE1:HE1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HF1:HF1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HH1:HH1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HD1:HD1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("HD1:HI1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("HN:HS").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HO1:HO1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HP1:HP1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HR1:HR1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HN1:HN1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("HN1:HS1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("HX:IC").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HY1:HY1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HZ1:HZ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IB1:IB1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"HX1:HX1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("HX1:IC1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("IH:IM").Select
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"II1:II1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IJ1:IJ1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IL1:IL1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Working Area").Sort.SortFields.Add Key:=Range( _
"IH1:IH1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Working Area").Sort
.SetRange Range("IH1:IM1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("IN:IN").Select
End Sub
Last edited: