VBA to insert sequential page numbers of ONLY visible worksheets

[SpencerRichman]

I have a workbook with 10 or so worksheets. The first worksheet is an 'input' page, and depending on what kinds of information the user inputs, some of the other worksheets either get hidden or unhidden.
At the bottom of the input page I have a button which then prints the workbook to a pdf. When it prints, it is only printing the visible worksheets, not the hidden ones.

I am attempting to figure out how to have page numbers appear on the bottom (footer) of the pages, but the page numbers need to start at 1 for the first VISIBLE worksheet and continue sequentially for all the VISIBLE worksheets so that the final pdf has page numbering that makes sense.

Any thoughts?

 

[John_w]

Presumably the first worksheet ('input') is visible but you don't want it to be included in the PDF?

Do the sheets already have a page footer?

 

[SpencerRichman]

Presumably the first worksheet ('input') is visible but you don't want it to be included in the PDF?

No, the VBA for the "print to pdf" hides the Input sheet before printing and then unhides it again afterwards. So in between the command to hide the worksheet and the command to print to pdf I need some code to number the pages.

 

[John_w]

It looks like you missed my question about page footers, which I added to my post just after you replied.

If the sheets already have a page footer then saving the entire workbook as a PDF should save only the visible sheets with the correct page numbering. This macro does that, with .Worksheets(1).Visible = xlSheetHidden hiding the Input sheet.

Public Sub Create_PDF()

    Dim PDFoutputFile As String
   
    Application.ScreenUpdating = False
   
    With ActiveWorkbook
        .Worksheets(1).Visible = xlSheetHidden
        PDFoutputFile = .Path & "\Pages.pdf"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFoutputFile, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Worksheets(1).Visible = xlSheetVisible
        .Worksheets(1).Select
    End With
   
    Application.ScreenUpdating = True
   
    MsgBox "Created " & PDFoutputFile
   
End Sub

If they don't have a page footer this macro adds a footer to every visible sheet and saves the workbook as a PDF.

Public Sub Create_PDF2()

    Dim PDFoutputFile As String
    Dim ws As Worksheet
       
    Application.ScreenUpdating = False
   
    With ActiveWorkbook
        PDFoutputFile = .Path & "\Pages2.pdf"
        .Worksheets(1).Visible = xlSheetHidden
   
        For Each ws In .Worksheets
            If ws.Visible Then
                ActiveWindow.View = xlPageLayoutView
                Application.PrintCommunication = False
                ws.PageSetup.CenterFooter = "&P of &N"
                Application.PrintCommunication = True
                ActiveWindow.View = xlNormalView
            End If
        Next
       
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFoutputFile, _
                                 Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
   
        .Worksheets(1).Visible = xlSheetVisible
        .Worksheets(1).Select
    End With
   
    Application.ScreenUpdating = True
   
    MsgBox "Created " & PDFoutputFile
   
End Sub