Method 'Range' of object '_Worksheet failed

[stratman]

Good morning Chandoo Team,

Can't figure out why this segment of code (in bold) keeps receiving a 1004 error; Method 'Range' of object '_Worksheet failed:

>>> use code - tags <<<

'Sort Table by Start Date
Sheets("Clockify").Select
    ActiveWorkbook.Worksheets("Clockify").ListObjects("Clockify").Sort.SortFields. _
        Clear
'    >>>> bolded
    ActiveWorkbook.Worksheets("Clockify").ListObjects("Clockify").Sort.SortFields. _
        Add Key:=Range("Clockify[[#All],[Start Date]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers
'    <<<< bolded
    With ActiveWorkbook.Worksheets("Clockify").ListObjects("Clockify").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I'm running the macro from a command button in a different tab. Just trying to sort Column G (dates) in ascending order.

Thanks in advance for the help


stratman

 

[vletm]

stratman​

Your Add Key:= Range refers to Your Activesheet instead of Worksheets("Clockify") ... or how?

 

[stratman]

Thanks for the reply vletm. So it should specifically refer to the Worksheet? I typically run macros from my PERSONAL file rather than put them directly in a workbook so this is a bit new to me. Here's a snapshot of the first couple lines in the table. Again, trying to sort Column G in ascending order. It worked fine when I manually did it and recorded the macro.

 

[vletm]

stratman​

As I wrote:
Your code tells to Excel that Add Key's Range is Your Activesheet.
I normally skip this kind of thread, because there are challenges to verify/test.
I would test something like below (instead of Your given code):

    With Sheets("Clockify")
        .ListObjects("Clockify").Sort.SortFields.Clear
        .ListObjects("Clockify").Sort.SortFields.Add Key:=.Range("Clockify[[#All],[Start Date]]"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        With .ListObjects("Clockify").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

... I cannot test above neither.
How did You do same as Your code manually,
if You see only and only other sheet than Clockify ?

 

[stratman]

Bingo. that worked vletm. Thanks for taking the time.

I wasn't sure how to VBA code a column sort so I recorded it with the macro recorder. Of course, it worked when I did it but when I ran the overall sub, it quit on the sort code it recorded.

This has been very helpful and instructive for future use. Much appreciated!