VBA Formatting Header with Cell Reference

[BlingGirl]

I am trying to write a Macro that will enter a couple of cell values from the current worksheet to the left and center page headers.

I started with the following and the Macro runs fine, pulls the values from the cells.

With ActiveSheet.PageSetup
.LeftHeader = Range("A1").Value
.CenterHeader = Range("I2").Value
End With

The problem is when I start to add formatting. When I add the following the Left Header runs fine and is in the correct format however, the center header doesn't show up at all.

With ActiveSheet.PageSetup
.LeftHeader = "&""Calibri,Italic""&9" & Range("A1").Value
.CenterHeader = "&""Calibri,Italic""&9" & Range("I2").Value
End With

If I remove the formatting from the center header and run with the following, the left header loads with proper formatting, and the center header will load but obviously italic.

With ActiveSheet.PageSetup
.LeftHeader = "&""Calibri,Italic""&9" & Range("A1").Value
.CenterHeader = Range("I2").Value
End With

I can't get around this they "must" be italic. Any help would be fantastic.

Office 365 Windows 10

 

[upsidedownhouse]

I have also had problems recently with VBA code not setting the headers and footers correctly. My fix was to treat each of the 6 places separately so in your case deal with the left header then in a separate 'with/end with' deal with the centre header. Worth a try.