Assigning Table/Header names in Sort Functions

Question

I have a code that works when I assign ranges manually, but this is a bit cumbersome especially when it's unclear if the sheet will be modified and therefore make the values in the code incorrect.

My original code

Const ProcName As String = "Filter_Ascending_Descending"
On Error GoTo Whoa

Dim WsCP As Worksheet: Set WsCP = Sheets("Cross Platform Database")
Dim WsDND As Worksheet: Set WsDND = Sheets("DO NOT DELETE")
Dim Header As Variant: Header = WsCP.Range("D36").Value
Dim CriteriaOp As Variant: CriteriaOp = WsCP.Range("C38").Value     '<< This will be the Data Value
Dim Criteria As Variant: Criteria = WsCP.Range("D38").Value         '<< This will be the Data Value
Dim Order As Variant: Order = WsCP.Range("C36").Value               '<< Expected to be Ascending/Descending
Dim OrderHeader As Variant: OrderHeader = "Table1[[#Headers],[Sales]]"

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'This code will sort the data in the relevant column according to the user selection
    If WsDP.Range("C26") = "Ascending" Then
        Sheets("Cross Platform Database").Range("S24:S71499").Sort _
          Key1:=Sheets("Cross Platform Database").Range("S23"), Order1:=xlAscending, Header:=xlYes

    ElseIf WsDP.Range("C26") = "Descending" Then
        Sheets("Cross Platform Database").Range("S24:S71499").Sort _
          Key1:=Sheets("Cross Platform Database").Range("S23"), Order1:=xlDescending, Header:=xlYes
    Else
        
    End If

SafeExit:
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
    Exit Sub

Whoa:
    Debug.Print "'" & ProcName & "' Run-time error '" _
      & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume SafeExit

End Sub

I tried to change all ranges to table names but my code jumps to error and returns

error 5 "Invalid procedure call or arguement"

'This code will sort the data in the relevant column according to the user selection
If Order = "Ascending" Then
    WsCP.ListObjects("Table1").Range("Table1[Sales]").Sort _
      Key1:=WsCP.Range(OrderHeader), Order1:=xlAscending, Header:=xlYes

Answer 1

Sort an Excel Table

Option Explicit

Sub TestSortTable()

    Dim WsCP As Worksheet: Set WsCP = Worksheets("Cross Platform Database")
    Dim AscDesc As String: AscDesc = CStr(WsCP.Range("C26").Value) ' unclear
    
    Dim tbl As ListObject: Set tbl = WsCP.ListObjects("Table1")
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Sales")
    
    Select Case LCase(AscDesc)
    Case "ascending"
        tbl.Range.Sort _
            Key1:=lcl.Range, Order1:=xlAscending, Header:=xlYes
    Case "descending"
        tbl.Range.Sort _
            Key1:=lcl.Range, Order1:=xlDescending, Header:=xlYes
    Case Else ' do nothing
    End Select

End Sub