set cell to today's date macro

[Guest]

I have a workbook where i want the date to be set in cell b23 to todays date every time the wkbook is opened
also every sheet in the workbook has a cell which requires this date but they are not all b23 can this be don

any help appreciate

 

[Tom Ogilvy]

use the Workbook_Open event in the ThisWorkbook Module

http://www.cpearson.com/Excel/events.htm

then include code like:

worksheets("Sheet1").Range("B23").Value = Date
worksheets("Sheet2").Range("C19").Value = Date


or just set it for B23 and have formulas in the other sheets in the
appropriate cells that point to B23 on Sheet1 (as an example.

You might also look at the worksheetfunction

=Today()

maybe that will give you what you want without coding.

--
Regards,
Tom Ogilvy

I have a workbook where i want the date to be set in cell b23 to todays

date every time the wkbook is opened.

also every sheet in the workbook has a cell which requires this date but

they are not all b23 can this be done

 

[Roger Whitehead]

Colm

In the ThisWorkbook Module:

Private Sub Workbook_Open()
With ThisWorkbook
.Sheets("Sheet1").Range("B23") = Date
.Sheets("Sheet2").Range("A23") = Date
.Sheets("Sheet3").Range("A2") = Date
'etc
End With
End Sub


--
HTH
Roger
Shaftesbury (UK)

I have a workbook where i want the date to be set in cell b23 to todays

date every time the wkbook is opened.

also every sheet in the workbook has a cell which requires this date but

they are not all b23 can this be done

 

[Guest]

Thanks Roger that worked a trea
col