Posts by jml2

jml2 · Jul 27th 2022

I'm trying to generate a header on my reports ("MASTER") tab. Right now it will pull the year selected from another tab and put the data into a String. I can get the String into the Header no problem. But I want to set the font, font size and both it and when I try to do that it doesn't work.

This is the code that works but is the wrong font.

Code

Dim StringOne As String
Dim StringTwo As String
Sheets("Overall Statistics").Select
StringOne = Range("$M$2").Value
StringTwo = StringOne & " END OF THE YEAR INJURY ASSESSMENT"
Sheets("MASTER").Select
Application.ActiveSheet.PageSetup.CenterHeader = StringTwo
Sheets("ROSTER").Select
Range("E7:F7").Select

This is the code that doesn't

Code

Worksheets("MASTER").PageSetup.CenterHeader = _
"&24&B&Calibri" & StringTwo

This what the record macro does when I do it manual:

Code

    Sheets("MASTER").Select
    Selection.Font.Bold = True
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""Arial Black,Bold""&24 2021 END OF THE YEAR INJURY ASSESSMENT"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .Zoom = 78
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Sheets("ROSTER").Select

Display More

This doesn't allow me to set the year based on the $M$2 cell in the Overall Statistics sheet

Any ideas?