Method 'Range' of object '_Worksheet' failed when trying to sort table

Question

I've trying to look for an answer on my error in Excel, what I try to do is to filter the contents in a column on a table from largest to smallest. I recorded a Macro doing this but whenever I put this code into a button I get an "Method 'Range' of object '_Worksheet' failed" after I try to use it.

  ThisWorkbook.Sheets("FDTHC").ListObjects("FilterDTHC").Sort.SortFields. _
    Clear

ThisWorkbook.Sheets("FDTHC").ListObjects("FilterDTHC").Sort.SortFields. _
    Add Key:=Range("Time"), SortOn:=xlSortOnValues, Order _
    :=xlDescending, DataOption:=xlSortNormal

With ThisWorkbook.Sheets("FDTHC").ListObjects("FilterDTHC").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

The VBA highlights the following part of the code:

ThisWorkbook.Sheets("FDTHC").ListObjects("FilterDTHC").Sort.SortFields. _
    Add Key:=Range("Time"), SortOn:=xlSortOnValues, Order _
    :=xlDescending, DataOption:=xlSortNormal

I appreciate any help given, thanks in advance.

Variatus Variatus · Accepted Answer · 2019-01-22T02:57:28

The range set in the command is on the ActiveSheet. It should be linked to Sheet("FDTHC").

ThisWorkbook.Sheets("FDTHC").ListObjects("FilterDTHC").Sort.SortFields.Add _
    Key:=ThisWorkbook.Sheets("FDTHC").Range("Time"), _
    SortOn:=xlSortOnValues, _
    Order:=xlDescending, _
    DataOption:=xlSortNormal

Method 'Range' of object '_Worksheet' failed when trying to sort table

1 Answers