What am I doing wrong?

[Patrick Simonds]

The code below works great if the active worksheet is Employee_List, but
if I call it from another active worksheet, I get a Sort referance not
valid. This is supoose to sort the data on the Employee_List worksheet,
while leaving me on the current (active worksheet)


Private Sub CommandButton1_Click()

'Sort by Paratransit Drivers First Name

Worksheets("Employee_List").Range("AA1").Value = 1

Dim wks As Worksheet
Set wks = Worksheets("Employee_List")
With wks.Range("A1:Z300")
..Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending,
Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal
End With

End Sub

 

[JMay]

Why not early in your existing code After the Dim Line, enter:
TempSheetName = Activesheet.name

then after the line

End with
enter:
Worksheets(TempSheetName).Activate

Would that work for you?

 

[Ken Johnson]

Hi Alan,
It worked for me after I added "wks." before each of the three sort
keys:

Key1:=Range("B2") becomes Key1:= wks.Range("B2")
Key2:=Range("C2") becomes Key2:=wks.Range("C2")
Key3:=Range("A2") becomes Key3:=wks.Range("A2")

Ken Johnson

 

[Ken Johnson]

Hi Patrick,
I don't know where I got the Name "Alan" from!

Ken Johnson

 

[Patrick Simonds]

Thank you for the quick fix.

 

[Ken Johnson]

Hi Patrick,
You're welcome, glad I could help.
Thanks for the feedback.
Ken Johnson

 

[Gary Keramidas]

patrick, that's what i had posted in your original post a few days ago:

..sort Key1:=wks.Range("D4"), Order1:=xlAscending, Key2:=wks.Range("A4"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=2, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal