Newbie sort problem - Please help

[deej]

I've spent hours googling and searching this NG but have been unable to
solve my problem, so would much appreciate any help.

I'm trying to autosort two ranges in a worksheet, and it works fine if I
record a macro and run it, but if I try to run it as an "event", an error
message comes up:

“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.”

Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Past Results").Select
Range("I2:L7").Select
Selection.Sort Key1:=Range("L3"), Order1:=xlDescending, Header:
=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("I9:K40").Select
Selection.Sort Key1:=Range("K10"), Order1:=xlDescending, Header:
=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select
End Sub

I would be grateful if someone could please tell me what I'm doing wrong.

Thanks

 

[deej]

try this, watch for wordwrap

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Worksheets("Past Results")
With ws
.Range("I2:L7").Sort Key1:=ws.Range("L3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Range("I9:K40").Sort Key1:=ws.Range("K10"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
ws.Range("A2").Select
End Sub

Thankyou very much Gary, worked perfectly.

 

[Gary Keramidas]

try this, watch for wordwrap

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Worksheets("Past Results")
With ws
.Range("I2:L7").Sort Key1:=ws.Range("L3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Range("I9:K40").Sort Key1:=ws.Range("K10"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
ws.Range("A2").Select
End Sub

 

[Gary Keramidas]

you're welcome.

--


Gary

Thankyou very much Gary, worked perfectly.