Sorting Tables (ListObjects) with VBA

[kakiebos]

Hi All.

I have a sheet with a number of Tables in it that I would like to sort on a regular basis.

Currently I have the following code which works, but I'm curious to know if there is a easier way with looping to sort all the tables in the sheet at once. Any suggestions?

Sub Sort_Lists()
'To sort all the list in the Lists sheet.


  Dim Ws As Worksheet


Set Ws = shLists
  With Ws
    .Select
    'Job Category Sorting
    .Range("Job_Category_Table[[#headers],[Job Category]]").Select
    With .ListObjects("Job_Category_Table").Sort.SortFields
      .Clear
      .Add Key:=Range("Job_Category_Table[[#all], [Job category]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Ws.ListObjects("Job_Category_Table").Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    
    'Job Number Information Sorting
    .Range("Job_Number[[#headers],[Job Number Information]]").Select
    With .ListObjects("Job_Number").Sort.SortFields
      .Clear
      .Add Key:=Range("Job_Number[[#all], [Job Number Information]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Ws.ListObjects("Job_Number").Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    
      'Public Holiday Sorting
    .Range("PublicHolidays[[#headers],[Date]]").Select
    With .ListObjects("PublicHolidays").Sort.SortFields
      .Clear
      .Add Key:=Range("PublicHolidays[[#all], [Date]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Ws.ListObjects("PublicHolidays").Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
      
  End With 'Ws
     
End Sub

 

[Fluff]

How about

Sub kakiebos()
   Dim i As Long
   Dim ary As Variant
   
   ary = Array("Job_Category_Table", "Job category", "Job_Number", "Job Number Information")
   With Sheet1
      For i = 0 To UBound(ary) Step 2
         With .ListObjects(ary(i)).Sort
            .SortFields.Clear
            .SortFields.Add Key:=.Parent.ListColumns(ary(i + 1)).DataBodyRange, SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
      Next i
   End With
End Sub

 

[kakiebos]

Thanks. That works like a charm.

 

[Fluff]

You're welcome & thanks for the feedback