Using variable table name for vba sorting

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
151
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...

Code:
    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:

Code:
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

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

Code:
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!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
151
My entire macro, written in CommandButton1, is as follows:

Code:
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

Board Regular
Joined
Apr 28, 2003
Messages
151
Bump. Hoping someone can help me with using the variable table name for vba sorting purposes.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top