Use VBA to add year to header for all sheets

[TAPS_MikeDion]

Hi everybody,
I have code to add the year to the header, but I need it to run through all sheets and it only works for the active sheet. Also, I wanted to have it use Arial, bold and font size 18. I've tried many different things that I thought might work, but they don't. I made the lines that aren't working into comments just to hang on to them for now. Please see below. Any help is greatly appreciated, thank you!

Sub header_year()

  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Sheets
    With ws
      With ActiveSheet.PageSetup
        '.CenterHeader.Font.Name = "Arial"
        '.CenterHeader.Font.Size = 18
        '.CenterHeader.Font.Bold = True
        .CenterHeader = Chr(10) & Format(Date, "yyyy") & " TRAFFIC DIVISION"
      End With
    End With
  Next ws

End Sub

 

[Joe4]

Your "ActiveSheet" never changes, as you are not selecting any other sheets!
Remove that reference, i.e.

Sub header_year()

  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Sheets
    With ws
      With .PageSetup
        '.CenterHeader.Font.Name = "Arial"
        '.CenterHeader.Font.Size = 18
        '.CenterHeader.Font.Bold = True
        .CenterHeader = Chr(10) & Format(Date, "yyyy") & " TRAFFIC DIVISION"
      End With
    End With
  Next ws

End Sub

 

[TAPS_MikeDion]

Hi Joe,
Thank you for that simple fix. Do you happen to know about the 2nd part of my question regarding having it use Arial, bold and font size 18?

Thanks!

 

[Joe4]

Hi Joe,
Thank you for that simple fix. Do you happen to know about the 2nd part of my question regarding having it use Arial, bold and font size 18?

Thanks!

Have a look here: Formatting and VBA codes for headers and footers

 

[TAPS_MikeDion]

Awesome, thank you again!

 

[Joe4]

You are welcome.
Glad I was able to help!