What am I doing wrong?

P

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
 
J

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?
 
K

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
 
K

Ken Johnson

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Suppressing a screen 4
Runtime error 1004 Application-defined or object defined error" on data sort 1
Calling another workbook 5
Sort error? 2
sort range 0
Sort by range 15
Writing a Sort Macro 4
Sort Question 5

Top