Below macro has been working great for me - but it's limited to only the table specified in the code. I've been trying to morph it to work in whatever table/sheet is active. I thought for sure my "ActiveTable" variable would work... What am i missing?
Before: (Works great, but only specified table)
Sub Optimal_Sort()
'
' Optimal_Sort Macro
'
Application.ScreenUpdating = False
'
ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort.SortFields.Add _
Key:=Range("Table4[[#All],[BL]]"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort.SortFields.Add _
Key:=Range("Table4[[#All],[STNAME]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort.SortFields.Add _
Key:=Range("Table4[[#All],[ACTUALSHIP]]"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Processing").ListObjects("Table4").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
After: (Doesn't work )
Sub Test_Sort()
'
' Test_Sort Macro
'
Dim ActiveTable As String
ActiveTable = ActiveSheet.ListObjects(1).Name
Application.ScreenUpdating = False
'
ActiveWorkbook.ActiveSheet.ActiveTable.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.ActiveTable.Sort.SortFields.Add _
Key:=Range(ActiveTable & "[[#All],[BL]]"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.ActiveTable.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.ActiveSheet.ActiveTable.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.ActiveTable.Sort.SortFields.Add _
Key:=Range(ActiveTable & "[[#All],[STNAME]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.ActiveTable.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.ActiveSheet.ActiveTable.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.ActiveTable.Sort.SortFields.Add _
Key:=Range(ActiveTable & "[[#All],[ACTUALSHIP]]"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.ActiveTable.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
Is this even the best way to sort these 3 fields in this certain order? The help is much appreciated.
Bookmarks