Sorting an inactive worksheet

[Murray Williams]

Is it possible to sort a worksheet that is not the active
sheet?

I'm using the following code that is in Book1 (the active
sheet) to try to sort a range of cells:

Workbooks("Book2").Sheets("Sheet1").Range("A3:Z100") _
.Sort Key1:=Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

When I try this I get the following error message:

"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."

If I activate Book2 either manually or by inserting the
following line prior to the lines listed above then
everything works as it should.

Windows("Book2.xls").Activate

It is not a problem to insert this line, but I was under
the impression that I should avoid using "activate"
and "select" when working with workbooks and worksheets
from within a macro to speed things up.

thanks,

Murray Williams

 

[Chip Pearson]

Murray,

Just use a fully qualified range reference in the Key parameter.
E.g.,


Workbooks("Book2").Sheets("Sheet1").Range("A3:Z100").Sort _
Key1:=Workbooks("Book2").Sheets("Sheet1").Range("B3"), _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)