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

Hi
Range("D1").Value = [D1]

HTH
Cordially
Pascal

a écrit dans le message de news:
...
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

Dang man, you guys rock! Thanks so much! I have been beating myself
trying to find a code that would do this and I never thought it would
have been that simple!

You're welcome, glad it helped ;-)

Cordially
PAscal

a écrit dans le message de news:
...
Dang man, you guys rock! Thanks so much! I have been beating myself
trying to find a code that would do this and I never thought it would
have been that simple!

You can assign the current data and time to a cell with code like

Range("A1").Value = Now

Format A1 to display the date and/or time desired.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


wrote in message
ups.com...
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