Sort reference is not valid

[Freddy]

I have read other post concerning sorting with macros, but I cannot find any
information as to why my code will not work. I am receiving the error message

“Run-time error ‘1004’:
The sort reference is not valid. Make sure that it’s within the data you
want to sort, and the first Sort By box isn’t the same or blank.â€

The sort code and the worksheet selection code is below. Does anyone have
any suggestions?

Thanks!

Worksheets("75851_Plasticity_All").Range("A:B").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Dave Peterson]

I would drop the .selects

with Worksheets("75851_Plasticity_All")
with .Range("A:B")
.Sort Key1:=.columns(2), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with
end with

And if you know if your data has headers, you don't have to let excel guess.
I'd put xlno or xlyes as the Header parm.

 

[Freddy]

Thanks Dave - This worked great!

I would drop the .selects

with Worksheets("75851_Plasticity_All")
with .Range("A:B")
.Sort Key1:=.columns(2), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with
end with

And if you know if your data has headers, you don't have to let excel guess.
I'd put xlno or xlyes as the Header parm.

 

[Freddy]

Hi Dave,

Do you have any thoughts on why the code you suggested below will work with
Excel XP but not with 2000?

Thanks,

Freddy

 

[Dave Peterson]

dataoption1 was added in xl2002.

Remove that parm and the preceding comma and it should work.

 

[Freddy]

That works. Thanks Dave!

dataoption1 was added in xl2002.

Remove that parm and the preceding comma and it should work.