Selecting a worksheet without making it active

[ordnance1]

How can I change my code below so the sort will perform without making
AAA-Supervisor the active shett?

Sheets("AAA-Supervisor").Select
range(Y2:Z24").Select
Selection.Sort Key1:=range("y4"). Order1:=x;Ascending, Header:=xlGuese, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,_
DataOption1:=xlSortNormal

 

[pswanie]

screenupdating.false?

and true after

 

[ordnance1]

But that does not keep the sheet from becoming Active.

 

[pswanie]

still kinda newb.... so pardon but what u mean with not making it active?

 

[Dave Peterson]

With workSheets("AAA-Supervisor")
with .range(Y2:Z24")
.cells.Sort Key1:=.columns(1), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,_
DataOption1:=xlSortNormal
end with
end with

And I bet you know your data. I wouldn't let excel guess whether you have
headers. I'd use xlyes or xlno.

ps. I think I got all your typos, but I didn't test it. It's better to copy
from the code window and paste directly into your message.

 

[Rick Rothstein \(MVP - VB\)]

Just join the Select/Selections together to eliminate them. After correcting
your syntax errors (I think I got them all), this should be equivalent to
what your posted code was doing (assuming you had the correct syntax
originally)...

Worksheets("AAA-Supervisor").Range("Y2:Z24").Sort Key1:=Range("y4"), _
Order1:=x_Ascending, Header:=xlGuese, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Rick

 

[Dave Peterson]

xlguese and x_ascending are still there and that range("y4") is unqualified.

Worksheets("AAA-Supervisor").Range("Y2:Z24").Sort _
Key1:=Worksheets("AAA-Supervisor").Range("y4"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal