If you have a lot of sheets to do this for, you can do something like
this in the code window for 'ThisWorkbook'...
Option Explicit
Const sDateRanges As String = _
"Package Summary Report:M10,Package Activity Report:M10"
Private Sub Workbook_Open()
Dim v As Variant, v1 As Variant
For Each v In Split(sDateRanges, ",")
v1 = Split(v, ":")
With Sheets(v1(0)).Range(v1(1))
If .Value = "" Then .Value = Date
End With
Next 'v
End Sub
...where sDateRanges is a comma delimited string of value pairs. The
value pairs are delimited with a colon.
--
If you want to do this for every sheet in the workbook then it would be
best to give the cell to get the date a local defined name so you can
ref the same cell regardless of its actual address location. This means
the cell address does NOT have to be "M10" on every sheet.
Example:
Each date cell on every sheet is named "DateRange" via the
Define Name dialog as follows:
Select the cell to get the date if empty at startup
In the name box type: 'Package Summary Report'!DateRange
Click OK
Select the next sheet and repeat for each, substituting the text
between the parenthesis (single quotes) with the actual sheet's name.
Now you can use the following code to set the date for all sheets...
Option Explicit
Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
With wks.Range("DateRange")
If .Value = "" Then .Value = Date
End With
Next 'wks
End Sub
--
-OR- you could do a combination of these by adding the defined name to
each sheet as described and leaving the date range out of the string,
as follows, if you only want to do this to specific sheets as in the
first example.
Option Explicit
Const sDateRanges As String = _
"Package Summary Report,Package Activity Report"
Private Sub Workbook_Open()
Dim v As Variant
For Each v In Split(sDateRanges, ",")
With Sheets(v).Range("DateRange")
If .Value = "" Then .Value = Date
End With
Next 'v
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc