Sorting attempt results in 400 error

[mcomp72]

I am suddenly getting a 400 error while trying to sort data on a worksheet, and I can’t figure out why. Here’s my code:

Dim SortRange As String
Dim KeySortCol As String
Dim LastColumn As String
Dim LastRow as Integer
 
ThisWorkbook.Sheets("Sheet 2").Activate
 
LastColumn = ThisWorkbook.Sheets("Sheet 5").Range("B80").Value
 
LastRow = ThisWorkbook.Sheets("Sheet 2").Cells(Rows.Count, 1).End(xlUp).Row
 
SortRange = "A2:" & LastColumn & LastRow
 
KeySortCol = "A2:A" & LastRow
 
ThisWorkbook.Sheets("Sheet 2").Range(SortRange).Sort key1:=Range(KeySortCol), order1:=xlAscending, Header:=xlNo

The 400 error happens on the last line.

I stepped through the code and watched the variables, and here is what they are equal to:

LastColumn = “BU”
LastRow = 5
SortRange = “A2:BU5”
KeySortCol = “A2:A5”

I had something similar working in an earlier version of this workbook, so I can’t figure out what I did that would suddenly cause the 400 error. Anyone have any ideas?

 

[mcomp72]

I tried moving the sub with this code from a Sheet module to a regular module, and the problem went away. I'm guessing you cannot do a sort in VBA from within a Sheet module?

 

[Yongle]

I'm guessing you cannot do a sort in VBA from within a Sheet module?

NO !!
- in a sheet module all unqualified ranges refer to that sheet
- "ACTIVATING" another sheet does not change that rule
- you have (inadvertently) mixed "apples" and "pears"
- the KEY range (in red) is NOT in your sort data (it is on a different sheet) - hence the error

This does not work UNLESS it is in the sheet module for "Sheet 2"

ThisWorkbook.Sheets("Sheet 2").Range(SortRange).Sort key1:=[COLOR=#ff0000]Range(KeySortCol)[/COLOR], order1:=xlAscending, Header:=xlNo

But this will work inside ANY sheet module

ThisWorkbook.Sheets("Sheet 2").Range(SortRange).Sort key1:=[COLOR=#ff0000]ThisWorkbook.Sheets("Sheet 2").Range(KeySortCol)[/COLOR], order1:=xlAscending, Header:=xlNo

 

[mcomp72]

Oh geez, I can't believe I missed that unqualified range. Sometimes when you stare at code too long, you miss the simplest things!

 

[Yongle]

The code does not contain an error. It is simply incomplete. So it does not jump out. We all fall foul of that problem!