Forum Discussion

Rachael Lock's avatar
Rachael Lock
Copper Contributor
Aug 19, 2018

Varying selected cells for a custom multi level sort macro

I use a multi-level sort for almost every spreadsheet I use at work.  Trouble is that every time they come in a minimum of 4 sections and I want it to sort & stay in those sections.  I have tried every select cell including dynamic suggestions that I can find but every time it lumps all of the selections together sorted.

 

tried, Ctrl A & Ctrl Shift right arrow, down arrow

other options I thought of was can you select a section and have the macro run on only that selection rather than the entire worksheet?

 

Current macro

Sub GSTsorting()
'
' GSTsorting Macro
' gst sorting on multi levels
'
' Keyboard Shortcut: Ctrl+r
'
Range("A1:G1").Select
Cells.Find(What:="GST on Income", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("B8:B117"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("D8:D117"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("A8:A117"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("GST Audit Report").Sort
.SetRange Range("A8:G117")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:G1").Select
Cells.Find(What:="GST on Expenses", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A120").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("B120:B520"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("D120:D520"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("A120:A520"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("GST Audit Report").Sort
.SetRange Range("A120:G520")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:G1").Select
Cells.Find(What:="GST Free Expenses", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("A523").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range("A1:G1").Select
Cells.Find(What:="BAS Excluded", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A1060").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("B1060:B1318"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("D1060:D1318"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("GST Audit Report").Sort.SortFields.Add2 Key:=Range _
("A1060:A1318"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("GST Audit Report").Sort
.SetRange Range("A1060:G1318")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:G1").Select
End Sub

No RepliesBe the first to reply

Resources