Help w/ Excel: How to retain last modified date?

[hardhank]

Hi Guru,

Question:

Overview: I've multiple sheets in my Excel spreadsheet. I insert date and time in the footer of every page in all sheets.

1. How do I retain the last modified date and time? Each time I open the file. the date and time change even though the file was NOT save. Hence, when I print the current date was printed out in the footer. How do I retain the last_modified date?

2. If the above is not possible to accomplish, I plan to hardcoded the date in the footer. How do I change the date in the footer for all sheets at once? Selecting "Select All Sheets" did NOT work. I still have to change each sheet one by one.

Any help/advice is greatly appreciated.

Thank you.
Jeff (Technical User)

 

[SkipVought]

Hi,

How do I change all footer? - Macro record changing one - loop thru all sheets to change all.

As you see, &Date and &Time are current values, you will have to insert the date and time values that you want. I'd store them on a hidden sheet. Use the FORMAT function to format in the footer.

Skip,

_[red][/red]

 

[hardhank]

I mean the tab (Sheet1, Sheet2, Sheet3, etc....) at the bottom of each worksheet. Inserting the date on Sheet1 just change the date on all sheets in Sheet1. It did NOT change the date in Sheet2, Sheet3, etc.....

How do I change the date on all Sheets in the spreadsheet?

Thanks,

 

[SkipVought]

Macro record changing one - loop thru all sheets to change all.

Skip,

_[red][/red]

 

[hardhank]

Thanks but the macro changes the left and center section as well. I've the date stamp on the right section in the header. The left and center sections of the header are specific to the Sheets but the date (right section) can be applied to all Sheets. I think I might be able to start from the recorded macro and plagiarize from there. I might have to use a Do....Until loop and set lots of variables.

Thanks for pointing me to the right direction. If you can think of an easier way to do this, pls let me know. Thanks again for your help.

 

[PHV]

A starting point:
strDate = "your date here"
For Each sh In ActiveWorkbook.WorkSheets
sh.PageSetup.RightFooter = strDate
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hardhank]

Thank you. That was a great help. Good day.