excel - Export multiple sheets to PDF simultaneously without using ActiveSheet or Select

Question

It has been drilled into my head, to avoid bugs and provide a good user experience, it is best to avoid using .Select, .Activate, ActiveSheet,ActiveCell, etc.

Keeping this in mind, is there a way to use the .ExportAsFixedFormat method on a subset of Sheets in a workbook without employing one of the above? So far the only ways I have been able to come up with to do this are to either:

1. use a For Each; however, this results in separate PDF files, which is no good.

2. use the code similar to that generated by the macro recorder, which uses .Select and ActiveSheet:

   Sheets(Array("Sheet1", "Chart1", "Sheet2", "Chart2")).Select
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
       "exported file.pdf", Quality:=xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=False, openafterpublish:= True
   

Perhaps it is impossible not to use ActiveSheet, but can I at least get around using .Select somehow?

I have tried this:

Sheets(Array("Sheet1", "Chart1", "Sheet2","Chart2")).ExportAsFixedFormatType:= _
    xlTypePDF, Filename:= "exported file.pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, openafterpublish:= _
    True

This produces:

error 438: Object doesn't support this property or method

Answer 1

Hate to dredge up an old question, but I'd hate to see somebody stumbling across this question resort to the code gymnastics in the other answers. The ExportAsFixedFormat method only exports visible Worksheets and Charts. This is much cleaner, safer, and easier:

Sub Sample()

    ToggleVisible False

    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "exported file.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

    ToggleVisible True

End Sub

Private Sub ToggleVisible(state As Boolean)
    Dim ws As Object

    For Each ws In ThisWorkbook.Sheets
        Select Case ws.Name
        Case "Sheet1", "Chart1", "Sheet2", "Chart2"
        Case Else
            ws.Visible = state
        End Select
    Next ws
End Sub