Date issue with macro

**Ivor** · 07-01-2010, 11:05 AM

Hello again my friends

Hope all are well.
I was wondering if someone could assist me with. I have a speadsheet which has a macro button assigned, where once clicked it will save that spreadsheet under the date listed in a cell (depicted as NOW()). The sheet saves under the current date at the time of saving (great...) but when I open the sheet, the date is showing as the current day (due to the NOW () reference attached). I was wondering is there of manipulating my code so that the date within a saved spreadsheet will remain as it was the day it was saved? here is the code attached:

Please Login or Register  to view this content.

Thanks guys

Ivor

**royUK** · 07-01-2010, 11:10 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Ivor** · 07-02-2010, 06:11 AM

Apologies Roy and all. I have now resent in the correct format.

Apologies again

Ivor

Please Login or Register  to view this content.

**pb71** · 07-02-2010, 08:09 AM

Try something like this in your code above ...

Please Login or Register  to view this content.

**Ivor** · 07-02-2010, 10:42 AM

Originally Posted by pb71

Try something like this in your code above ...

Please Login or Register  to view this content.

Hello

Thank you for your code. Whilst this may save the file with the date and time of saving, when I re-open the file (say on another day), the date within the file will still have changed to the current day.

Ok say for example the command 'NOW()' resides in cell A1 on my spreadsheet, although that command will always display the current date, what I need is a piece of code which once the document has been saved, the date displayed by the 'NOW()' command will remain the same (i.e. disabling the 'NOW()' command). So something like:

Upon saving the spreadsheet, then the date displayed in cell A1 remains as it is (therefore disabling the 'NOW()' command.

Is there such a code?

Sorry if I have confused the issue with my explanation.

Ivor

**pb71** · 07-02-2010, 12:37 PM

The date_time in cell G3 should remain the same when you open the workbook saved using the macro and will only change if you run the macro again. It sounds like you are reopening the latest file (saved in your specified location) and running the macro from this file.

With, for example:

Please Login or Register  to view this content.

How about something like this, using the workbook name to define the date and time stamp in cell A1 when the workbook is opened:

Please Login or Register  to view this content.

Copy and paste the code into the ThisWorkBook module in the Visual Basic Editor.

**pb71** · 07-02-2010, 03:07 PM

I've taken another look at your code above and something like this should do it:

Please Login or Register  to view this content.

No need for the workbook open event.

**Ivor** · 07-05-2010, 07:33 AM

Originally Posted by pb71

I've taken another look at your code above and something like this should do it:

Please Login or Register  to view this content.

No need for the workbook open event.

Well, what can I say!!! Thank you very much PB71, your code works perfectly and is ideal for me to work from. As a newbie to VBA its hard enough getting to grips with 'how to even start writing code from an idea', so by using your example I can start to understand where to start from.

Thanks again for your help

Cheers and have a great week sir

Ivor