Do you mean when last saved? EDIT: you can do this with code. If the window is maximised, you should see an Excel icon to the left of 'File' in the menu bar. Rightclick it & choose 'View Code'. Paste the following into the window that appears (the workbook module): Private Sub WorkbookBeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ActiveSheet.PageSetup.LeftFooter = 'Last saved: ' & Format(Date, 'dd-mm-yy') & ' ' & Time End Sub Press ALT+Q to quit the Visual Basic Editor. Then save the file & check the footer in Print Preview. A follow-up question: is there a modification to this code to have it apply to all worksheets in a workbook.
Aug 13, 2018 - Excel spreadsheets have the capacity to either automatically update dates or keep them static. Whether your spreadsheet needs a current date in Excel or an. Excel from the current Office 365 for both PC and Mac, back to Excel 2007. To insert a date in Excel that changes with the current date, you can.
E.g., I have a workbook with four worksheets. When I did the above, it dutifully put the Last Saved into the worksheet that was active when I saved the workbook. However, it did not add the footer to the other worksheets. I then switched to a different worksheet, and saved the Excel workbook again. The second worksheet now gets the footer, but with a timestamp that is different from the first worksheet. I.e., the first worksheet does not get the updated timestamp.
Is there a way to ensure all worksheets get footers reflecting the same date and time? Sorry for the delayed response, I took the weekend off from thinking. Can you provide me some more details on what you are suggesting? I.e., when I right-click on the Excel icon, and click View Code, the top left panel has a folder(Microsoft Excel Objects) with a bunch of nodes under it for each of the worksheets in my workbook. I clicked on one of the nodes, copied and pasted the below in, and did ALT-Q to get out of VBA Editor.
Then I saved the Excel workbook, and printed out the worksheet in question, and there is no footer on it, with the Date/Time last modified. Should there be? Incidentally, I'm on Excel 2002. Sheet.PageSetup.LeftFooter = 'Last saved: ' & Format(Date, 'dd-mm-yy') & ' ' & Time Next Sheet. You've got 2 bits slightly wrong.
'the top left panel has a folder(Microsoft Excel Objects) with a bunch of nodes under it for each of the worksheets in my workbook. I clicked on one of the nodes.' This sounds like you're putting the code in one of the work sheet modules. It needs to go in the work book module. Under the Objects folder, you've got Sheet1, Sheet2, etc.
The bottom one should be ' ThisWorkbook'. That's where you need it.
Once you're 'there', you'll know from the title bar - BookWhatever ThisWorkbook (Code) 2. You need all 3 code lines, i.e.: For Each Sheet In ThisWorkbook.Sheets Sheet.PageSetup.LeftFooter = 'Last saved: ' & Format(Date, 'dd-mm-yy') & ' ' & Time Next Sheet Have another go. Shout if you're still stuck & I'll upload a sample.
OK, I seem to have it working. I was just putting in the three lines of VBA code you had up there, without surrounding it by the first and last line (i.e., Private Sub WorkbookBeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) and End Sub Once I put all five lines of code in there, it's working. Does that make sense?
![Modified Modified](/uploads/1/2/5/3/125379257/453481547.png)
I'm not a VBA coder, as you can probably tell. So, is this basically adding a macro to the workbook?
What triggers this running? When you open the workbook?
Just so I know, will this cause that dialogue box to pop up whenever I open the workbook, the one about enabling macros? That's correct. Essentially macros (AKA sub-procedures) have 3 bits - a start line ( Sub Whatever), an end line ( End Sub), and the actual code in the middle. It's a BeforeSave event procedure, so it'll run every time (just before) you perform a save. It will cause that dialogue box to pop up whenever you open the workbook. The only way round that is to have a macro to open the file stored in your 'Personal Macro Workbook' and then assign that macro to a custom button (or keyboard shortcut).
Interesting stuff tho', eh?