How to have a custom date (YYYY) automatically updated in the footers (Excel 2010) upon opening a file

[csauv]

Dear All,

I would be grateful for your kind help. I am trying to have the year in the footers of all my Excel files to automatically update to the current year. To that effect, I have created the following macro:

Sub SetFooterAttributes()
For Each xWorksheet In ActiveWorkbook.Worksheets
xWorksheet.PageSetup.LeftFooter = & Format(Now, "YYYY")

Next xWorksheet

End Sub

I have applied this to 2012 files, thinking that I would not have to do it again when the files would be opened in 2013, as the code states 'now'. I was wrong!

How can I fix the footer so that the year is automatically updated please?

Thank you.

C.

 

[VoG]

Try this in the ThisWorkbook module

Private Sub Workbook_Open()
For Each xWorksheet In Me.Worksheets
    xWorksheet.PageSetup.LeftFooter = Format(Now, "YYYY")
Next xWorksheet
End Sub

 

[csauv]

Thank you! That seems to do the trick!