1. Adding a File Path and Fileanme
If you need to show the current workbook's filename and path in a header or footer, you can use the following
Path: &[Path]
File Name: &[File]
Page: &[page]
Total pages: &[pages]
Name of worksheet: &[tab]
If you need to insert the file path and file name into a cell, try the following
=CELL("filename")
If you want to strip out the brackets and the sheet name, you can use the following variation on the formula:
=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")
2. Show the "Ampersands &" in header or footer, you need to &&
e.g. Tom && Jerry
it will be shown as "Tom & Jerry"
3. Add auto "last modified date"
You need to have a macro as the following
Private Sub Workbook_SheetChange(ByVal _
Sh As Object, ByVal Target As Excel.Range)
ActiveSheet.PageSetup.CenterFooter = _
"Worksheet Last Changed: " & _
Format(Now, "mmmm d, yyyy hh:mm")
End Sub
Or the following as last saved date
Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
Dim sht
For Each sht In Sheets
sht.PageSetup.CenterFooter = _
"Workbook Last Saved: " & _
Format(Now, "mmmm d, yyyy hh:mm")
Next
End Sub
With font and font size assigned
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sht
For Each sht In Sheets
sht.PageSetup.RightHeader = _
"&""Calibri""&8" & _
"Updated on: " & _
Format(Now, "mmmm d, yyyy")
Next
End Sub
The best way to accomplish the task above is to add a macro to the ThisWorkbook object that is triggered just before a workbook is saved.
For Windows, how to create a Macro
https://support.office.com/en-us/article/Create-or-delete-a-macro-2841ba85-9da6-4bd7-93a4-0da90844f283?ui=en-US&rs=en-US&ad=US&fromAR=1#bmcreatemacrovba
