Loop through all sheets in workbook

[jennie]

Hi,

Very simply I want a bit of code to run on every worksheet in
workbook every time it is opened and I can't remember how to loo
through the worksheets on a workbook open event.
The workbooks name changes a lot so looping through the active workboo
would be nice.

Thanks
Jenni

 

[Kaak]

For i = 1 to Sheets.count

Next

 

[Leith Ross]

Hello Jennie,

Copy either one of these routines and add in the code you want to run.


Code
-------------------

Dim I As long

With ThisWorkbook
For I = 1 To .Worksheets.Count
'Insert your code here
Next I
End With

'Or this method...

Dim Wks

For Each Wks In ThisWorkbook.Worksheets
'Insert your code here
Next Wks

 

[jennie]

Thank you both very much
Jennie

 

[jennie]

Why doesn't this work?

Private Sub Workbook_Open()

Dim i As Long

With ThisWorkbook
For i = 1 To Worksheets.Count
Range("A1").Value = "Hello"
Next i
End With


End Su

 

[Kaak]

I think you want to do this

Private Sub Workbook_Open()

Dim i As Long

For i = 1 To Worksheets.Count

Sheets(i).select

Range("A1").Value = "Hello"

Next i

End Su

 

[jennie]

Thank you
It now writes Hello on each sheet!
Thanks a lot
Jenni

 

[Tushar Mehta]

No need to select objects.

If you use Worksheets.Count in the loop control statement, use the same
in the body of the loop.

Private Sub Workbook_Open()
Dim i As Long
For i = 1 To Worksheets.Count
Worksheets(i).Range("A1").Value = "Hello"
Next i
End Sub

--
Regards,

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