Loop Through worksheets
 

Hi All,

I need a VBA code for looping through all the worksheets and add th
=today() is cell A1, except in sheet "Overview".

Please help:)

Stephe

--
Message posted from http://www.ExcelForum.com

Loop Through worksheets
 

Dim sh as Worksheet
for each sh in Activeworkbook.Worksheets
if lcase(sh.name) < "overview" then
sh.range("A1").Formula = "=Today()"
' or for a hard coded date
' sh.Range("A1").Value = Date
sh.range("A1").NumberFormat = "mm/dd/yyyy"
end if
Next

--
Regards,
Tom Ogilvy

"Stephen " wrote in message
...
Hi All,

I need a VBA code for looping through all the worksheets and add the
=today() is cell A1, except in sheet "Overview".

Please help:)

Stephen


---
Message posted from http://www.ExcelForum.com/

Loop Through worksheets
 

Stephen, give this a try.

Troy

Sub AddToday()
Dim ii As Integer

For ii = 1 To Worksheets.Count
If Worksheets(ii).Name < "Overview" Then
Worksheets(ii).Range("A1").Formula = "=today()"
End If
Next ii
End Sub


"Stephen " wrote in message
...
Hi All,

I need a VBA code for looping through all the worksheets and add the
=today() is cell A1, except in sheet "Overview".

Please help:)

Stephen


---
Message posted from http://www.ExcelForum.com/

Loop Through worksheets
 

Thank you all. The code works great!!

Stephen

TroyW wrote:
*Stephen, give this a try.

Troy

Sub AddToday()
Dim ii As Integer

For ii = 1 To Worksheets.Count
If Worksheets(ii).Name < "Overview" Then
Worksheets(ii).Range("A1").Formula = "=today()"
End If
Next ii
End Sub


"Stephen " wrote in message
...
Hi All,

I need a VBA code for looping through all the worksheets and ad
the
=today() is cell A1, except in sheet "Overview".

Please help:)

Stephen


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com

Loop Through worksheets
 

The 2 replies to the original question brings up a couple of points
about VBA programming I do not understand, hope someone can enlighten
me.
1. Why dim a variable (in this case "sh") to replace Worksheets? Why not
just work directly with the built in value as the second response does?
2. Once having "Dim sh as Worksheet" why is it that the statement "for
each sh in Activeworkbook.Worksheets" automatically assigns sh all of
the worksheets since sh has been defined as only a (one) worksheet?
Thanks

"Stephen <" wrote:

Hi All,

I need a VBA code for looping through all the worksheets and add the
=today() is cell A1, except in sheet "Overview".

Please help:)

Stephen

---
Message posted from http://www.ExcelForum.com/

Loop Through worksheets
 

Hi
both ways are possible but Tom's solution using a
worksheet object has the following benefits (IMHO)
- easier to write using an object reference
- you could use this reference to store the old object and
use it later in your code

Also using a for ... each construct is possible. This
statement will loop through all worksheets in the
collection 'worksheets' and assigns the current loop value
to this object variable


-----Original Message-----
The 2 replies to the original question brings up a couple
of points
about VBA programming I do not understand, hope someone
can enlighten
me.
1. Why dim a variable (in this case "sh") to replace
Worksheets? Why not
just work directly with the built in value as the second
response does?
2. Once having "Dim sh as Worksheet" why is it that the
statement "for
each sh in Activeworkbook.Worksheets" automatically
assigns sh all of
the worksheets since sh has been defined as only a (one)
worksheet?
Thanks

"Stephen <" wrote:

Hi All,

I need a VBA code for looping through all the
worksheets and add the
=today() is cell A1, except in sheet "Overview".

Please help:)

Stephen

---
Message posted from http://www.ExcelForum.com/

.