A time and Date Formula

Hello.
Here is what I want to do.
I have a centralized document that myself and my co-workers use. I would like to insert a date formula that upon opening the "Main" document displays the current date (that is no problem). The catch is, when myself or my co-workers make a change to that document, and "save as" I would like that date to stick (no longer display the current date, but the date in which it was "saved as").

Ideas?

Hmmm, do you always "Save As" thus the original document is more of a template? If so, we can put simple code in the workbook that will convert that cell formula (=TODAY()) to it's value. However, if someone resaves the template, you'll lose your formula.

Edit: if you save your original file as a xlt (template), then there's less danger of people overwriting it.

there could be other ways to do so.. but right now the most simplest one that comes to my mind is by using Macro..

you can use the following code..

Suppose the name of the sheet in which the date is stored is "abc"
In VBA-->Microsoft Excel Objects-->ThisWorkbook, write the following code

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


Sheets("abc").activate
Cells.select
selection.copy
Cells(1,1).select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
application.cutcopymode = false



End Sub

c.vaibhav,

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

You can paste this code (similar to c.vaibhav's) into the Workbook object of the VBA Editor. This is written assuming your "=TODAY()" formula is in sheet1 cell A1

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("Sheet1").Range("A1").Copy
    Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlValues

End Sub

Does this work for you?

I found the workbook object but how do I go about pasting your code in there?


EDIT* I found the object and pasted the code. But I am unable to save the template with the formula in there. I am having to go back into the template and redo the formula.