Date Stamp in a template sheet

[SRMPURCHASE]

I want a date to be entered in cell H12 (=NOW() formatted for DD/MMM/YY) when a template file is opened, when the file is saved with a new name I want H12 to display that saved date when the file is opened again.
I've searched for an answer, lot's of VBA out there for date and time stamping but could not find one to make this happen.

 

[Yongle]

Why do you not want cell H12 updated at the same time as template is saved under new name?
How is save as new name controlled - is it done via macro or is user clicking manually on SaveAs?

 

[SRMPURCHASE]

Let's say it this way, Cell H12 formatted as DD-MMM-YY to get =NOW() when the file is opened.
When the file is saved with a new name I want that date to reflect when the file was originally opened for all future viewings.
does that help?

 

[Yongle]

What is the answer to my 2nd question?

 

[SRMPURCHASE]

File-Save As manually

 

[SRMPURCHASE]

Ideally I would like the user to be forced to save with a new name and location as well, leaving the template on the directory available for the next use.

 

[Yongle]

Do you want a macro which
a) saves the file with a new name and H12 changed to today's TimeStamp
b) saves the file with new name (with H12 left blank) AND where TimeStamp auto-inserted when file is reopened

 

[SRMPURCHASE]

My setup is a user opens a template file, enters data where applicable, H12 displays the current date automatically.
When prompted or manually, they are to save the file in another location with another name, leaving the totally blank template available for the next user. I have not solved how to make H12 static with the date is all.

I use this for all sorts of order forms that are emailed to vendors.

 

[SRMPURCHASE]

Looks like A would be the best choice