asked on

Excel 2010 Macro Print header failing.

I have an Excel 2010 sheet that i have a macro that prints various forms out for.
The macro hides some columns and then prints the form, then puts the columns back in.

I set the .headerleft But what comes out is allways missing most of the header text, varying amounts of the end characters eg. sometimes i get "Seni" next time i might get "Senior Scruit"

I have tried setting this at the end of the script and at the beginning, with no change.

Any idears on how to make this stick?

Cheers
Andrew

Sub SenScuit()
'
' Scruit Macro
'
    Call ClassSort1
'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
'    Application.PrintCommunication = True
'    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = "&B&""Times New Roman""&14 Senior Scruitineer Form"
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .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)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .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
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    Columns("C:D").Select
    Selection.EntireColumn.Hidden = True
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Columns("I:M").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" & LRow
    ActiveSheet.ResetAllPageBreaks
    Range("A1:N" & LRow).Select
    Range("N" & LRow).Activate
    Call Borders
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate _
        :=True, IgnorePrintAreas:=False

    ActiveSheet.ResetAllPageBreaks
    Columns("A:Z").Select
    Selection.EntireColumn.Hidden = False
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andrew Davis

ASKER

Thanks Michael.

I uncommented back the method calls and put back the range variable (LRow) and it all worked fine.

Frustratingly i cannot see really why it shouldnt work in mine except i did read somewhere that sometimes too many PageSetups can cause issues. So i did try cutting out all those that did nothing, but didnt go quite as far as you did. And it still happens.

But with yours it works fine.

As you probably can see i started with a macro record and then edited as needed. I should have tried stripping it back and cleaning it completely. I suppose i could now start putting back bit by bit..... But i am lazy ;)

Thanks for your help.

Cheers
Andrew

Andrew Davis

ASKER

Just as a quick follow up.
In the method that i had striped back i had just left

    With ActiveSheet.PageSetup
        .LeftHeader = "&B &""Times New Roman""&14 Senior Sign ON"
        .CenterHeader = ""
        .RightHeader = ""
        .FitToPagesWide = 1
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With

Open in new window

As it turned out it was the fact that i had left the .CentreHeader and .RightHeader that was still throwing it.

Cheers

Andrew Davis

ASKER

Question Below Deleted as i found the answer.

If your up to the challange i have another question at https://www.experts-exchange.com/questions/27870080/in-a-macro-can-i-tell-if-a-cell-is-hidden.html

Really wish the original developer would let me just convert this all to a Database... ;)

Cheers
Andrew