Access Beginner
Active Member
- Joined
- Nov 8, 2010
- Messages
- 311
- Office Version
- 2016
- Platform
- Windows
Hi All,
I've recorded a macro that sorts about 19 tables (used to create charts).
I was wondering if there is a more efficient way to write this code? And if someone could have a crack at it?
I've recorded a macro that sorts about 19 tables (used to create charts).
I was wondering if there is a more efficient way to write this code? And if someone could have a crack at it?
Code:
Sub SortZoneCharts()
'
' SortZoneCharts Macro
'
'
Sheets("Chart_Tables").Select
Range("F287:G301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"G288:G301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("F287:G301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("H287:I301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"I288:I301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("H287:I301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J287:K301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"K288:K301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("J287:K301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("L287:M301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"M288:M301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("L287:M301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N287:O301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"O288:O301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("N287:O301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("P287:Q301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"Q288:Q301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("P287:Q301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("R287:S301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"S288:S301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("R287:S301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"U288:U301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("T287:U301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll ToRight:=7
Range("V287:W301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"W288:W301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("V287:W301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"Y288:Y301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("X287:Y301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("Z287:AA301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AA288:AA301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("Z287:AA301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AB287:AC301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AC288:AC301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AB287:AC301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AD287:AE301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AE288:AE301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AD287:AE301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AF287:AG301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AG288:AG301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AF287:AG301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AH287:AI301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AI288:AI301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AH287:AI301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AJ287:AK301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AK288:AK301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AJ287:AK301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AL287:AM301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AM288:AM301"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AL287:AM301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AN287:AO301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AO288:AO301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AN287:AO301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AP287:AQ301").Select
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chart_Tables").Sort.SortFields.Add Key:=Range( _
"AQ288:AQ301"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chart_Tables").Sort
.SetRange Range("AP287:AQ301")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AQ304").Select
End Sub