I am respectfully requesting VBA code to do the following...

I would like a macro that would change the contents of the cell to be
a date. Currently the cell reads today's date via =NOW(). I want
this to be permanent. That is, if I close the sheet and come back a
few days later, the cell will now read that day's date. I want it to
be the previous date...and always be that date.

The user opens a spreadsheet and it is automatically formatted to show
the date for today. The user inputs information. My macros are set
up so that when the user closes the file, that sheet is copied to
another tab so that when the user opens the file the next day, a fresh
tab opens with that day's date, but the one they worked on yesterday
is archived for future reference. Again, on the archived one, I don't
want the cell to read =NOW() because the date will change...and
subsequently all the saved tabs would all have the same date...NOW().
Hopefully this makes sense.

I suppose having the user input the date as a string somehow would be
the easiest, but my users aren't the most computer literate and any
step I can eliminate in the process makes my job that much easier.
Any help is greatly appreciated. Thanks in advance.

Example to make sure I'm clear...

User opens the .xls file...
Cell D1 shows the date (Friday, June 22, 2007) via cell contents
=NOW()...
User inputs some information and exits...
A macro runs that copies the sheet, saves it as a new archived
sheet...
User opens the file the next day and the new sheet shows cell D1 as
Saturday, June 23, 2007, but *the archived sheet from June 22 should
still read Friday, June 22, 2007*

Thanks