Excel Tips: Headers, Footers, Marcos (Updated)

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