• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

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

0
Andrew Davis
Asked:
Andrew Davis
  • 3
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
I cut back your script to test

Sub SenScuit()
'
' Scruit Macro
'
   'Call ClassSort1 
   With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    With ActiveSheet.PageSetup
        .LeftHeader = "&B&""Times New Roman""&14 Senior Scruitineer Form"
    End With
    Columns("A:A").EntireColumn.Hidden = True
    Columns("C:D").EntireColumn.Hidden = True
    Columns("G:G").EntireColumn.Hidden = True
    Columns("I:M").EntireColumn.Hidden = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" & 10
    ActiveSheet.ResetAllPageBreaks
    Range("A1:N" & 10).Select
    Range("N" & 10).Activate
   'Call Borders 
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    ActiveSheet.ResetAllPageBreaks
    Columns("A:Z").EntireColumn.Hidden = False
End Sub

Open in new window


and the header printed fine. Please try uncommenting the method calls  and give this a try

Michael
0
 
Andrew DavisManagerAuthor Commented:
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
0
 
Andrew DavisManagerAuthor Commented:
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
0
 
Andrew DavisManagerAuthor Commented:
Question Below Deleted as i found the answer.


If your up to the challange i have another question at http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27870080.html

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

Cheers
Andrew
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now