Create a Page Header using a Macro.. NOT working.

[mateo]

I have a employee who wants to create an Excel page header using a macro (shown below... CenterHeader in BOLD). I am by no means a macro master but the ticket eventually got to me and since I am stuck I am posting it here.

With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""&14 My Company Name&""Arial,Regular""&10" & Chr(10) & "&""Arial,Bold""&12 Agriculture Shop Operations Summary&""Arial,Regular""&10" & Chr(10) & "&12For the week ending January 15, 2012" & Chr(10) & ""
.LeftHeader = ""
yada yada yada....
End with

When you run the macro it all looks good except the CenterHeader part ends at For the week ending, it doesn't show the January 15, 2012? Run it again and your results are a bit different, still with most of it working but the final row being cut off like For the week end

I read about a character limitation for .CenterHeader but I did a check and think we are in bounds. My user and the first level support said that if you step through the macro in the editor it works, but not when you run it (I just realized I did NOT confirm that claim, but do believe them).

I asked about why he would hardcode the date in the macro instead of using a variable but that is what they want to do.

I tried shortening the string, removing the font sizes, mixed and matched and after awhile had to move on to other things and gave up.

Everyone testing this issues is running Office 2010 on a Windows 7 machine and the issue has been reproduced on all 3 machines.

I can enter data in a single, merged cell, and make that a Header by referencing that cell in the macro, but then all the fonts and font sizes are the same.

What am I missing? Any other alternative suggestions?

thanks in advance.

 

[TMShucks]

Works for me. I get:

My Company Name
Agriculture Shop Operations Summary
For the week ending January 15, 2012

with the appropriate formatting.

 

[mateo]

Hummmm.. the plot thickens.

Thanks TMShucks!