Sort Each Worksheet

[Guest]

Why is this not working? I want to do a sort on each page in the workbook.
The workbook is comprised of several (100+) worksheets. Here's my code:

Sub SortAlpha()

Dim ws As Worksheet

Application.StatusBar = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Please Wait, Sorting Report..."

Range("A6").Select
Range("A7:I65536").Sort Key1:=Range("A7"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Cells.Replace What:="BY REQUEST DATE", Replacement:= _
"BY NAME", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:= _
True, SearchFormat:=False, ReplaceFormat:=False
Range("A1:I1").Select

Next ws
Set ws = Nothing
Application.StatusBar = False

End Sub

 

[Guest]

question: Does this code do absolutely nothing are do you recieve an error
message?

 

[Tushar Mehta]

*How* is it not working?

Based on my guess, the easiest way, though not necessarily the smart
way, would be to insert a ws.activate statement before the range
("a6").select.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Sub SortAlpha()

Dim ws As Worksheet

Application.StatusBar = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Application.StatusBar = "Please Wait, Sorting Report..."

Range("A6").Select
Range("A7:I65536").Sort Key1:=Range("A7"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Cells.Replace What:="BY REQUEST DATE", Replacement:= _
"BY NAME", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:= _
True, SearchFormat:=False, ReplaceFormat:=False
Range("A1:I1").Select

Next ws
Set ws = Nothing
Application.StatusBar = False

End Sub

 

[Bob Phillips]

Not tested, but try qualifying it

Sub SortAlpha()

Dim ws As Worksheet

Application.StatusBar = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Please Wait, Sorting Report..."

ws.Range("A6").Select
ws.Range("A7:I65536").Sort Key1:=ws.Range("A7"), _
Order1:= xlAscending, _
Header:= xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _

Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal
ws.Cells.Replace What:="BY REQUEST DATE", Replacement:= _
"BY NAME", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:= _
True, SearchFormat:=False, ReplaceFormat:=False
ws.Range("A1:I1").Select

Next ws
Set ws = Nothing
Application.StatusBar = False

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)