Using variable table name for vba sorting

[melodramatic]

I have created a macro that, after an editor finishes their edits and is ready to re-protect them, goes through each data sheet, sorts the table, protects and hides the sheet. It then goes on to the next sheet/table on the list.

The problem I'm having is in using the variable table name in the sort feature.

When I first created the macro, I did my norm and went thru the steps, using the first sheet/table in the list. Here's what it gave me...

    ActiveWorkbook.Worksheets("KCO US").ListObjects("T_KCO_US").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("KCO US").ListObjects("T_KCO_US").Sort.SortFields. _
        Add2 Key:=Range("T_KCO_US[[#All],[EFFECTIVE DATE]]"),  _ 'THIS IS THE LINE THAT'S GIVING ME PROBLEMS W/VARIABLE NAME USE
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("KCO US").ListObjects("T_KCO_US").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

When I went to run that coding with the variables, I made the following line, which does NOT work:

Add2 Key:=Range(t_name&("[[#All],[EFFECTIVE DATE]]")), _

I get "Compile error: Type-declaration character does not match declared data type"

I've also tried the following (I found it somewhere online...), which gives me a Run-time error 1004: Method 'Range' of object '_Worksheet' failed

Add2 Key:=Range(t_name & "[EFFECTIVE DATE]"),  _

Add2Key:=Range(t_name&"[[#All],[EFFECTIVE DATE]]"), _

I'm at the give up and ask for help point. Does anyone have an idea as to how I can do this? Thanks in advance!

 

[melodramatic]

My entire macro, written in CommandButton1, is as follows:

Private Sub CommandButton1_Click()

'Written by Melody October May
'Originally Launched 2020-11-11

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim user As String
Dim pwd As String
Dim wsnum As Long
Dim wsrow As Long
Dim wscount As Long
Dim ws_name
Dim t_name

    If Range("Q1") = 0 Then
        GoTo UnProtect_Data
    Else
        GoTo Protect_Data
    End If
    
UnProtect_Data:

user = Environ("username")
pwd = Range("L1")

Range("M1").Value = user

    If Range("N1") = False Then
        MsgBox "Editing of this file is restricted to Legal & Contracts.  Should you need assistance, please contact someone in those two departments.", vbOKOnly
        Exit Sub
    End If

    wsnum = Range("O1") '4
    wsrow = Range("P1") '22
    
        For wscount = wsrow + 1 To wsrow + wsnum
            ws_name = Worksheets("Coding").Range("C" & wscount)
            t_name = Worksheets("Coding").Range("B" & wscount)
            Sheets(ws_name).Visible = True
            Sheets(ws_name).Unprotect Password:=pwd
        Next wscount
        
    Worksheets("Query").Select
    Range("Q1").Value = 1
    CommandButton1.Caption = "Lock Data"
        
    MsgBox "All Data Sheets are now visible and unprotected for editing.  Please remember to Lock Data before closing out file" & vbNewLine & vbNewLine & _
            "When the Lock Data is performed, the system will automatically re-sort all data by client name and contract effective date."
            
    Exit Sub

Protect_Data:

pwd = Range("L1")
wsnum = Range("O1")
wsrow = Range("P1")
    
    For wscount = wsrow + 1 To wsrow + wsnum
        ws_name = Worksheets("Coding").Range("C" & wscount)
        t_name = Worksheets("Coding").Range("B" & wscount)
        MsgBox "WS = " & ws_name & " / Table = " & t_name ' I used this to double-check that the correct worksheet & table names are loading. They are
            ActiveWorkbook.Worksheets(ws_name).ListObjects(t_name).Sort.SortFields.Clear
            ActiveWorkbook.Worksheets(ws_name).ListObjects(t_name).Sort.SortFields. _
                Add2 Key:=Range(t_name & "[EFFECTIVE DATE]"), SortOn:=lSortOnValues, _
                xOrder:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets(ws_name).ListObjects(t_name).Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            ActiveWorkbook.Worksheets(ws_name).ListObjects(t_name).Sort.SortFields. _
                Add2 Key:=Range(t_name & "[COMPANY NAME]"), SortOn:=xlSortOnValues _
                , Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets(ws_name).ListObjects(t_name).Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        Sheets(ws_name).Protect Password:=pwd
        Sheets(ws_name).Visible = xlSheetVeryHidden
    Next wscount
    
    Sheets("Coding").Visible = xlSheetVeryHidden
    Worksheets("Query").Select
    Range("Q1").Value = 0
    Range("R1").Value = 0
    CommandButton1.Caption = "Edit Data"
    CommandButton2.Caption = "Edit Coding"
    Range("E9").Select
    
    MsgBox "All Data Sheets are now Locked." & vbNewLine & vbNewLine & "Additionally, all data has been re-sorted by client name and contract effective date."
    
    Exit Sub
      
End Sub

 

[melodramatic]

Bump. Hoping someone can help me with using the variable table name for vba sorting purposes.