Get a true "Saved date"

[Chad S]

I would like to put a true "Saved Date" in the header or footer of my Excel spreadsheets. I would like for this "saved Date" to automatically update everytime I save the document and reflect the date the file was saved.

Using the "date" function just gives me the current date. So when I pull of a file the header shows the current date rather than the date it was last saved. So if I saved a file on "01/12/2005" and opened it on 02/05/2005 the "date" function would show the current date of "02/05/2005".

I have read a little on the internet about writing a custom macro the will execute when the save command is selected and will populate the header with the current date just prior to saving the file. However the info that I found on the internet did not specifically tell how to build and implement the macro.

Can anyone help with this problem?

Thanks.

Chad

[SPenney]

Chad---

I use the record button to get most of my macros.

Tools >> Macros >> Record.

While recording, type =today(). Enter. Then select the cell again, copy, right click, paste special values.

Stop recording.


Or this code will do it too.


ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select

But this is in a cell, not the header or footer.

Hope this helps

Stacie

[Chad S]

Stacie,

Thanks for the info but does this enable the spreadsheet to automatically update the date every time the file is saved?

I want it to be automatic so that I don't forget to run a macro and have the "saved date" be out of sync with my true revisions.

Chad

[eluehmann]

Put this in:

alt+F11 to open VB

Go to this workbook and double click

enter in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select

End Sub

This will time stamp the cell which is active.

You can change

ActiveCell.FormulaR1C1 = "=TODAY()"

to

sheets("sheet1").range("a1").formular1c1 = "=today()"

to timestamp cell A! on sheet 1 every time... hope this helps

[eluehmann]

or try this for a date and time stamp:

Sheets("sheet1").Range("A1").FormulaR1C1 = "=now()"

format as m/d/yy h:mm:ss AM/PM;@