The folowing code is used to auto define the header/footer while plotting an excel worksheet to pdf. Only thing that doesn't seem to work is the centerfooter function, it's supposed to give "active sheet name - defined date in dd-mm-yyyy format (value of cell Sheets("instellingen").Cells(22, 2) ), this results only in returning the date in format "mm-dd-yyyy" in the centerfooter, ignoring and not returning the activesheet name part.
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Company name"
.RightHeader = ""
.LeftFooter = Sheets("instellingen").Cells(20, 2).Value
.CenterFooter = Activesheet.name & " - " & Sheets("instellingen").Cells(22, 2).Value
.RightFooter = "Pagina &P van de &N"
Thanks :)
Edit. (full macro)
Sub PlotPDF()
'
' PlotPDF Macro
'
' Sneltoets: Ctrl+Shift+P
'
Application.PrintCommunication = False
Application.Dialogs(xlDialogPrinterSetup).Show
With ActiveSheet.PageSetup
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$A:$N"
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Company name"
.RightHeader = ""
.LeftFooter = Sheets("instellingen").Cells(20, 2).Value
.CenterFooter = ActiveSheet.Name & " - " & Format(Sheets("instellingen").Cells(22, 2).Value, "dd-MM-yyyy")
.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
.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
On Error Resume Next
– TomMsgBox (Activesheet.name & " - " & Sheets("instellingen").Cells(22, 2).Value)
? – VityataActiveSheet.Name & Chr(10) & Format(Sheets("instellingen").Cells(22, 2).Value, "dd-MM-yyyy")
Don't know what makes the difference though :) – Tefalpan