2

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
Jake C
  • 49
  • 1
  • 8

1 Answers1

4

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.

BigBen
  • 46,229
  • 7
  • 24
  • 40