Public Sub SortTheData() Call FilterWorksheet(ActiveSheet) lRow = Get_lRow(ActiveSheet) Depot = FindHeaderCol("1:1", "Depot") Area = FindHeaderCol("1:1", "Area") Location = FindHeaderCol("1:1", "Location") Pallet = FindHeaderCol("1:1", "Pallet") Workorder = FindHeaderCol("1:1", "Workorder") Box = FindHeaderCol("1:1", "Box") With ActiveSheet.AutoFilter.Sort .SortFields.Clear .SortFields.Add Key:=Range(Depot & "2:" & Depot & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SortFields.Add Key:=Range(Area & "2:" & Area & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SortFields.Add Key:=Range(Location & "2:" & Location & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SortFields.Add Key:=Range(Pallet & "2:" & Pallet & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SortFields.Add Key:=Range(Workorder & "2:" & Workorder & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SortFields.Add Key:=Range(Box & "2:" & Box & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
Sort Sheet by Column B
Note: This has issues like only 2 or three keys max I think. See updated method using Sort.
Dim lRow As Long lRow = Cells(Rows.Count, 2).End(xlUp).Row Range("A2:D" & lRow ).Sort key1:=Range("B2:B" & lRow ), order1:=xlAscending, Header:=xlNo ' Sort A2:DLRow by Column B
https://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column