Loop through all sheets in workbook
 

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

Loop through all sheets in workbook
 

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

Loop through all sheets in workbook
 

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

Loop through all sheets in workbook
 

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

Loop through all sheets in workbook
 

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

Loop through all sheets in workbook
 

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

Loop through all sheets in workbook
 

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

Loop through all sheets in workbook
 

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