Excel VBA Sorting in Table Error 1004 "Method 'Range' of Object '_Worksheet' failed

Question

I am working with a table in excel and would like to sort the data in it by the 'Date Submitted'. However, I keep getting the error

"Method 'Range' of Object '_Worksheet' failed.

I have tried numerous ways of accomplishing this but all return the same error.

The code is being written in a separate sheet from the sheet containing the table as that may be a source of the error.

Below is a brief overview of my most recent attempt to resolve this.

Sheets("Database").ListObjects("DatabaseTable").Sort.SortFields.Clear
Sheets("Database").ListObjects("DatabaseTable").Sort.SortFields.Add Key:=Range("DatabaseTable[Date Submitted]"), Order:=xlAscending
Sheets("Database").ListObjects("DatabaseTable").Sort.Apply

No, the workbook contains three sheets. The code is in 'Sheet1' and the ListObject is in 'Sheet2'. Would best practice be to put this into its own module and call the routine instead? — Jake C

BigBen BigBen · Accepted Answer · 2019-09-27T16:37:24

With unqualified Range calls in a sheet code module, the implicit qualifier is Me, i.e. the sheet containing the code, or Sheet1 - see this question.

So

Range("DatabaseTable[Date Submitted]")

is equivalent to

Me.Range("DatabaseTable[Date Submitted]")

or

Sheet1.Range("DatabaseTable[Date Submitted]")

Since the table is on Sheet2, you need:

Sheet2.Range("DatabaseTable[Date Submitted]")

In summary (from the linked question):

The easy way to avoid having to remember any of this is to always fully qualify any Worksheets, Sheets, Range, Cells, or Names reference.*

N.B. Columns and Rows need qualifying too.

Excel VBA Sorting in Table Error 1004 "Method 'Range' of Object '_Worksheet' failed

1 Answers