Home Header/footer unable to overwrite when plotting
Reply: 0

Header/footer unable to overwrite when plotting

user3026
1#
user3026 Published in April 27, 2018, 8:32 am

I created a excel vba script, mostly by using the record button and manually adding the preset headers and footers.

The problem that's occuring is that it seems to be unable to overwrite existing headers and footers. The code works fine if the header/footers are manually deleted at first before plotting using the macro (included below) little bit anoying and have not really got a clue how to solve it, are there any functions available to just delete all the existing printsettings before executing this script?

Application.PrintCommunication = False
    Application.Dialogs(xlDialogPrinterSetup).Show
    With ActiveSheet.PageSetup
        ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.PageSetup.PrintArea = "$A:$N"
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With

    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Project X"
        .RightHeader = ""
        .LeftFooter = Sheets("instellingen").Cells(20, 2).Value
        .CenterFooter = ActiveSheet.Name & Chr(10) & Format(Sheets("instellingen").Cells(22, 2).Value, "dd-MM-yyyy")
        .RightFooter = "Pagina &P van de &N"
        .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
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .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
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309418 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO