loop through all worksheets
 

My workbook has over 100 worksheets. I want to be able to execute the code
below, have it cycle through all worksheets and when done leave the
worksheet that was active when the code was started active.

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Activate
.Range("A3").Activate
End With
Next n
Application.ScreenUpdating = True

loop through all worksheets
 

Sub LoopAllWS()
Dim ws As Worksheet
Dim wsStart As Worksheet

Set wsStart = ActiveSheet
For Each ws In Worksheet
With ws
'No need to use Select use .With code here
End With
Next ws
wsStart.Activate
End Sub




--
Regards
Dave Hawley
www.ozgrid.com
"ordnance1" wrote in message
...
My workbook has over 100 worksheets. I want to be able to execute the code
below, have it cycle through all worksheets and when done leave the
worksheet that was active when the code was started active.

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Activate
.Range("A3").Activate
End With
Next n
Application.ScreenUpdating = True

loop through all worksheets
 

Thanks, but I get an Object Required error and the following line of text is
highlighted:

For Each ws In Worksheet



"ozgrid.com" wrote in message
...
Sub LoopAllWS()
Dim ws As Worksheet
Dim wsStart As Worksheet

Set wsStart = ActiveSheet
For Each ws In Worksheet
With ws
'No need to use Select use .With code here
End With
Next ws
wsStart.Activate
End Sub




--
Regards
Dave Hawley
www.ozgrid.com
"ordnance1" wrote in message
...
My workbook has over 100 worksheets. I want to be able to execute the
code below, have it cycle through all worksheets and when done leave the
worksheet that was active when the code was started active.

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Activate
.Range("A3").Activate
End With
Next n
Application.ScreenUpdating = True

loop through all worksheets
 

There is a typo in the statement, use this:

For Each ws In Worksheets

Regards,
Per

"ordnance1" skrev i meddelelsen
...
Thanks, but I get an Object Required error and the following line of text
is highlighted:

For Each ws In Worksheet



"ozgrid.com" wrote in message
...
Sub LoopAllWS()
Dim ws As Worksheet
Dim wsStart As Worksheet

Set wsStart = ActiveSheet
For Each ws In Worksheet
With ws
'No need to use Select use .With code here
End With
Next ws
wsStart.Activate
End Sub




--
Regards
Dave Hawley
www.ozgrid.com
"ordnance1" wrote in message
...
My workbook has over 100 worksheets. I want to be able to execute the
code below, have it cycle through all worksheets and when done leave the
worksheet that was active when the code was started active.

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Activate
.Range("A3").Activate
End With
Next n
Application.ScreenUpdating = True

loop through all worksheets
 

On May 19, 10:27*am, "ordnance1" wrote:
My workbook has over 100 worksheets. I want to be able to execute the code
below, have it cycle through all worksheets and when done leave the
worksheet that was active when the code was started active.

Application.ScreenUpdating = False
Dim n As Single
* * For n = 1 To Sheets.Count
* * * * With Sheets(n)
* * * * * * .Activate
* * * * * * .Range("A3").Activate
* * * * End With
* * Next n
Application.ScreenUpdating = True

Probably you want to activate the first cell of each sheet so that
user get it right A1 on reaching sheets


You can use following code for speed

Sub YourSubName()
Dim ws As Worksheet
Dim wsStart As Worksheet

Set wsStart = ActiveSheet
For Each ws In Worksheet
If ws.Visible = xlSheetVisible Then
Application.GoTo ws.Range("a1"), True
End If
Next ws
wsStart.Activate
End Sub

loop through all worksheets
 

Thanks

"Per Jessen" wrote in message
...
There is a typo in the statement, use this:

For Each ws In Worksheets

Regards,
Per

"ordnance1" skrev i meddelelsen
...
Thanks, but I get an Object Required error and the following line of text
is highlighted:

For Each ws In Worksheet



"ozgrid.com" wrote in message
...
Sub LoopAllWS()
Dim ws As Worksheet
Dim wsStart As Worksheet

Set wsStart = ActiveSheet
For Each ws In Worksheet
With ws
'No need to use Select use .With code here
End With
Next ws
wsStart.Activate
End Sub




--
Regards
Dave Hawley
www.ozgrid.com
"ordnance1" wrote in message
...
My workbook has over 100 worksheets. I want to be able to execute the
code below, have it cycle through all worksheets and when done leave
the worksheet that was active when the code was started active.

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Activate
.Range("A3").Activate
End With
Next n
Application.ScreenUpdating = True