Select method of range class failed

[Anurag]

I was tryning to sort a range in an inactive sheet in excel using the
following code:


Sheets("Terr Plan data").Range("AD8:AH29").Select
If Sheets("Terr Plan data").Cells(2, 36) = 1 Then
Selection.Sort Key1:=Sheets("Terr Plan data").Range("AF4"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Else
Selection.Sort Key1:=Sheets("Terr Plan data").Range("AH4"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


When i try to run it it gives an error: "Select method of range class
failed". Can anyone help me find with this.

 

[Andy Pope]

Hi,

It will fail if 'Terr Plan Data' is not the active sheet. The select
method will not change sheets AND select a range. If you must have the
range selected then activate the sheet first.

You should be able to do the sort without making the sheet active or
selecting the range. This is untested but should work.

With Sheets("Terr Plan data").Range("AD8:AH29")
If Sheets("Terr Plan data").Cells(2, 36) = 1 Then
.Sort Key1:=Sheets("Terr Plan data").Range("AF4"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Else
.Sort Key1:=Sheets("Terr Plan data").Range("AH4"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End With

Cheers
Andy