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

--
jenni
-----------------------------------------------------------------------
jennie's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=47936

For i = 1 to Sheets.count

Next

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=47936

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

-------------------


Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47936

Thank you both very much
Jennie


--
jennie
------------------------------------------------------------------------
jennie's Profile: http://www.excelforum.com/member.php...fo&userid=6706
View this thread: http://www.excelforum.com/showthread...hreadid=479367

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

--
jenni
-----------------------------------------------------------------------
jennie's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=47936

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

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=47936

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

--
jenni
-----------------------------------------------------------------------
jennie's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=47936

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

In article ,
says...

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 Sub


--
Kaak
------------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
View this thread: http://www.excelforum.com/showthread...hreadid=479367