Export to pdf user selected sheets

Question

I'm pretty new to VBA. I have a summary page named "Summary" and then 60 pages named "Pg (1)", "Pg (2)" ...etc.

I want the user to select multiple pages and then press a button to export the selected pages to a pdf. The only code I have at the moment is

    Sub PDFSummary()

    Sheets("Summary").Select
    Sheets("Summary").Activate
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
        
    Sheets("Summary").Select
     
End Sub

This only creates the PDF for the summary page at the moment though.

score 2 · Answer 1 · answered Jan 28 '22 at 13:22

Create an array of the selected sheet names, add in Summary and reselect.

Sub PDFSummary()

    Dim ws As Worksheet, ar
    Dim n As Long, sFilename As String
    
    n = ActiveWindow.SelectedSheets.Count
    ReDim ar(0 To n) As String
    ar(0) = "Summary"
    n = 0
    For Each ws In ActiveWindow.SelectedSheets
        n = n + 1
        ar(n) = ws.Name
    Next
    Sheets(ar).Select
    Sheets("Summary").Activate
    
    sFilename = "Sheets.pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sFilename, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
   
End Sub

Export to pdf user selected sheets

1 Answers1