CODEWITHSUNDEEP
vbaexcelpdfspreadsheet
Calico
Calico

Reputation: 416

PDF'ing Workbook - Page numbers algined to PDF instead of Sheet

I have a workbook with multiple sheets. Some sheets have more than 1 page, so I have the following in the footer Page &[Page] of &[Pages] to display the number of pages in each sheet.

I then PDF them using the code below:

Sub PDFSheets()
    Dim ans As Variant
    Dim fPath As String
    Dim Team As String
    ans = MsgBox("Please ensure the sheets you would like to PDF are visible before running this macro. Files will be saved to your Desktop." & Chr(10) & Chr(10) & "Do you wish to continue?", vbYesNo + vbInformation, "PDF Sheets")

    If ans = vbYes Then
        Sheets("FrontSheet").Visible = True
        Sheets("Launcher").Visible = False
        Team = Sheets("FrontSheet").Range("E21").Value
        fPath = CreateObject("WScript.Shell").specialfolders("Desktop")
        Call SelectAllSheets
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "/" & Format(Now(), "yyyymmdd") & Team & " VM", _
        openafterpublish:=True, ignoreprintareas:=False
        Sheets("Launcher").Visible = True
        Sheets("FrontSheet").Visible = False
    Else
        Exit Sub
    End If
End Sub

Example scenario: -Sheet 1 has 1 page, no footer -Sheet 2 has 5 pager, with the footer Page &[Page] of &[Pages] - The PDF will then output 2 of 6 on the bottom of sheet 2

This is adhering to the rules of the PDF and not the page numbers in the sheet. Any ideas how I can get it to out put 1 of 5 instead (as shown on the workbook)?

Any help is appreciated Calico

Upvotes: 0

Views: 1689

Answers (1)

Lisa
Lisa

Reputation: 552

Try this.

With Worksheets("Launcher").PageSetup
    .RightFooter = "&""Arial""&10 Page &P of " & .Pages.Count       
End With

Also, go to Page Layout -> Page Setup, in the Page tab, set First page number to 1.

Upvotes: 1

Related Questions