Sort a list object identified by a variable

Okay all, I'm just having trouble figuring out the syntax for this. I know if you have a table called "Table1" whose first column is headed "Column1", you can sort it by doing the following:

With Table1
     .Sort.SortFields.Add Range("Table1[[#All],[Column1]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

How do you use this syntax if you have only identified the table with a variable? The code I'm using identifies a worksheet based on a selection on the worksheet called "Dashboard", then identifies the table on that worksheet and setting a variable to it. Here's how I wrote that code:

Dim Part As String: Part = Dashboard.Range("B7").Value
Dim ws As Worksheet: Set ws = ActiveWorkbook.Worksheets(Part)
Dim tbl As ListObject: Set tbl = ws.ListObjects(1)

So now I want to sort the table that is identified by the variable "tbl". But how do I rewrite the code for that? I've tried this:

With Table1
     .Sort.SortFields.Add Range(tbl"[[#All],[Column1]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers
End With

...and this...

.Add Range("tbl[[#All],[Column1]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers

...and this...

.Add Range(tbl[[#All],[Column1]]), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers

So now I'm at a loss. How do I do this?

Originally Posted by Leith Ross

Hello Jerbinator,

When you Set a variable, you are setting a pointer to an Object. This variable will now behave like the object you have assigned to it.

Your code then would be...

With Tbl
     .Sort.SortFields.Add Range("Table1[[#All],[Column1]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

The problem with this code is that it's still identifying the table as "Table1", and not as the variable table. The previous code I have written could end up identifying any of over fifty tables. When I tried this code, it gave me runtime error 1004 "Method 'Range' of object '_Global' failed". And I know my variable is identifying the right table because I've got checks in to make sure it does.

Originally Posted by Leith Ross

Hello Jerbinator,

Here is a macro that will sort all Tables on "Sheet1" in ascending order using column 1.

Sub SortTables()

    Dim Tbl As ListObject
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Sheet1")
        
        For Each Tbl In Wks.ListObjects
            With Tbl.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Tbl.Range.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .Apply
            End With
        Next Tbl
        
End Sub

Leith, I retooled this a little bit and it works great. There's no need for me to sort multiple tables because there's only one table per worksheet, but the variable part of it worked perfectly. Thank you!