Changing Font Type in Header VBA Code

[RobR1981]

Hello Everyone,

I'm getting some odd behavior when I try running some VBA code that updates the font type in a page header. Here is the snippet of code:

With masterWkbk.Worksheets("Dashboard")
     .PageSetup.RightHeader = "&""Times New Roman,Regular" & Format(Now, "YYYY") & " " & prevQtr & qtrEndingDate
End With

Running the macro places September 30, 2019 in the header.

If I remove the "&""Times New Roman,Regular" portion, I get the correct result:

2019 3rd Quarter / Ending September 30, 2019

Can't figure out what I am doing wrong.

The prevQtr and qtrEndingDate are of course variables and defined above that line of code.

 

[John_w]

Try this:

        .PageSetup.RightHeader = "&""Times New Roman,Regular"" " & Format(Now, "YYYY") & " " & prevQtr & qtrEndingDate

If you want an explicit font size (e.g. 8):

        .PageSetup.RightHeader = "&""Times New Roman,Regular""&8 " & Format(Now, "YYYY") & " " & prevQtr & qtrEndingDate

 

[RobR1981]

Thank you! Worked perfectly!