Sort Problem

[Patrick Simonds]

I want to sort all worksheets without selecting them. I was attempting to
alter some code I got from Norman Jones which allowed me to perform other
functions with out actually selecting the worksheet, but it only seems to
sort the active worksheet. What am I missing?



Dim SH As Worksheet

For Each SH In ActiveWorkbook.Worksheets

Range("A3:h1301").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Next SH

 

[Leith Ross]

Hello Patrick,

The code as it is written is defaulting to the ActiveSheet
(Range("A3:h1301").Select). Notice the words Select and Selection in
the code are not qualified with a worksheet name. Without the
qualifier, the system defaults to the ActiveSheet. Here is the amended
code.
_____________________

Dim SH As Worksheet

For Each SH In ActiveWorkbook.Worksheets

SH.Range("A3:h1301").Sort Key1:=Range("A4"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Next SH
_____________________

Sincerely,
Leith Ross

 

[Guest]

An unqualified Range defaults to the active sheet. So the statement:

Range("A3:h1301").Select

just selects this range in the active sheet. If you have 3 sheets then your
code, since it refers to the selected range, sorted this same range in the
active sheet 3 times.

There is seledom a need to select anything. Just identify it and do whatever
to the identified object (range, chart, shape etc.). Selecting is essentially
just a screen event and it limits what you can do because you can't select
something that is not in the active sheet. It also slows code execution. For
example, to clear the contents of range A1:A10 on sheet ("Data") if this is
NOT the active sheet:
Sheets("Data").Range("A1:A10").ClearContents

Changed code:
Dim SH As Worksheet
For Each SH In ActiveWorkbook.Worksheets

SH.Range("A3:h1301").Sort Key1:=SH.Range("A4"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, atchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Next SH

Regards,
Greg

 

[Nigel]

You will also need to qualify the sortkey to the appropriate sheet e.g......

SH.Range("A3:H1301").Sort Key1:=SH.Range("A4"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal