Dynamically change Sort key value in table code

[rbeaslin]

I have a workbook with numerous sheets formatted identically with tables. First column is date, sorted ascending. I want to loop through the sheets, selecting the table by name (each has its own table name), sort on the "date" column. The code I have written to this point doesnt like the Key:= as is shown below. I have tried other methods and havent found a solution. I ran the macro recorder to match and the Key should be like "tablename[Date]". I cant get that to be dynamically changing in the code.

Sub SortTables()
Dim tbl As ListObject
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
    If sht.Name = "Data" Or sht.Name = "Summary" Or sht.Name = "Import" Or sht.Name = "Backup" Then
        'do Nothing
    Else
            For Each tbl In sht.ListObjects
                sht.Activate
                Worksheets(sht.Name).ListObjects(tbl.Name).Sort.SortFields.Clear
                Worksheets(sht.Name).ListObjects(tbl.Name).Sort.Add [COLOR=rgb(184, 49, 47)][B]Key:=tbl.Name & "[Date][/B][/COLOR]", SortOn:=xlSortOnValues, Order:=xlAscending
                With sht.ListObjects(tbl.Name).Sort.SortFields
                    .Header = xlYes
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
            Next tbl
    End If
Next sht
End Sub

Thank you

 

[Fluff]

How about

   For Each tbl In sht.ListObjects
      sht.Activate
      tbl.Sort.SortFields.Clear
      tbl.Sort.SortFields.Add Key:=tbl.ListColumns("Date").Range, SortOn:=xlSortOnValues, Order:=xlAscending
      With tbl.Sort
         .Header = xlYes
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
   Next tbl

 

[rbeaslin]

Exactly what I needed. I wasn't aware of the use/capabilities of .listcolumns.

Thank you, Fluff!

 

[Fluff]

You're welcome & thanks for the feedback