VBA Help - Working with Pagesetup Function - Issues with Setting Header Font Size

[Johnny Thunder]

Hi gang,

I am working with some code that sets print formats for a summary page and I am encountering the strangest thing.

Issue

Whenever I run the code on files that contain filenames that have numbers in them (example: 300 Racks) the .PageSetup.CenterHeader code that I have gets overwritten and makes the font size 409 instead of the size 24 I am specifying.

Not sure whats going wrong but it is like hit or miss when I run the code on different files.

Here is the code, see anything that could be causing the issue?

Any help is appreciated

Sub FormatPrint2(LastRow, MaxCurr)
'----------------------------------------------------------------------------------------------------------------------
'--- 4. This subroutine defines the print area and formats the printed sheets
'----------------------------------------------------------------------------------------------------------------------
Dim HeaderName As String
Dim HeadText As String
Dim Tall As Integer
Dim Dimension As String
Dim PrintEnd As Integer


PrintEnd = LastRow + MaxCurr + 1
ActiveSheet.PageSetup.PrintArea = "$B$1:$P" & PrintEnd


HeaderName = Range("H11").Value
Tall = ActiveSheet.PageSetup.Pages.Count + 2
Dimension = "{1," & Tall & "}"
Application.PrintCommunication = False


With ActiveSheet
    'Setup for the Print Area and Format of Margins
    .PageSetup.Zoom = False
    .PageSetup.LeftMargin = Application.InchesToPoints(0.75)
    .PageSetup.RightMargin = Application.InchesToPoints(0.75)
    .PageSetup.TopMargin = Application.InchesToPoints(1)
    .PageSetup.BottomMargin = Application.InchesToPoints(1)
    .PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
    .PageSetup.FooterMargin = Application.InchesToPoints(0.5)
    .PageSetup.CenterHeader = "&C&24" & HeaderName '<----------------------------Drops the File Name in Center Header-----------------------"
    .PageSetup.CenterFooter = "&P/&N"
    .PageSetup.RightFooter = "&D &T"
    .PageSetup.FitToPagesWide = 1
    .PageSetup.FitToPagesTall = 5
    .PageSetup.Orientation = xlLandscape 'or xlPortrait
    .PageSetup.PaperSize = xlPaperLetter
    .PageSetup.PrintTitleRows = "$1:$10"
    .PageSetup.LeftFooter = "&""Calibri,Regular""&11Confidential"
    .PageSetup.RightFooter = "&""Calibri,Regular""&11&P/&N"
    .PageSetup.CenterHorizontally = True
    .PageSetup.CenterVertically = False
    .PageSetup.BlackAndWhite = False
    .PageSetup.FirstPageNumber = xlAutomatic
End With


Application.PrintCommunication = True


End Sub

 

[Norie]

The number at the start of the filename is being included in the font size, try putting a space after 24.

.PageSetup.CenterHeader = "&C&24 " & HeaderName

 

[Johnny Thunder]

Woohoo! that was it, and honestly I don't think I would have ever spotted it.

Thanks again!

 

[Norie]

When you check the headers manually by going to Page Setup it doesn't show you but if you enter this in the immediate window after running your code you will.

?ActiveSheet.PageSetup.CenterHeader

 

[Johnny Thunder]

Thanks for the tip. I appreciate the help on this.