Loop Through worksheets

[Stephen]

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

 

[Tom Ogilvy]

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

 

[TroyW]

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]

Thank you all. The code works great!!

Stephen

*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

 

[noyb]

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

 

[Frank Kabel]

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