PDA

View Full Version : For Each / Next Problem



rrenis
10-11-2007, 07:25 AM
Hi all - I have the following code which I though would loop through all sheets in the workbook. Unfortunately it only works on the active sheet. Can anyone point out where I'm going wrong please? Thanks.

Sub SortSheets()
Dim ws As Object
For Each ws In ThisWorkbook.Sheets
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
With ws.UsedRange
.Hyperlinks.Delete
End With
Range("A65536").End(xlUp).Offset(1, 0).Select
Next ws
End Sub

Cheers,
rrenis

p45cal
10-11-2007, 08:01 AM
Simplest, codewise is to add 'ws.select'
Sub SortSheets()
Dim ws As Object
For Each ws In ThisWorkbook.Sheets
ws.select
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
With ws.UsedRange
.Hyperlinks.Delete
End With
Range("A65536").End(xlUp).Offset(1, 0).Select
Next ws
End Sub

rrenis
10-11-2007, 08:08 AM
:bow:

Thanks!!!
:beerchug:
Cheers,
rrenis

Norie
10-11-2007, 09:07 AM
There is no need to use select and it'll probably slow things down if you do.

Just make sure you reference ws within the loop when referring to things like Cells.

Sub SortSheets()
Dim ws As Object
For Each ws In ThisWorkbook.Sheets
ws.Cells.Sort Key1:=ws.Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
With ws.UsedRange
.Hyperlinks.Delete
End With
Application.Goto ws.Range("A65536").End(xlUp).Offset(1, 0), True
Next ws
End Sub

rrenis
10-11-2007, 09:52 AM
Thanks Norie!! :cloud9:

Cheers,
rrenis