Select Multiple Excel Sheets to Export to PDF

Question

I have some code written to build a list of worksheets in a string based on if the user selects a checkbox to include the sheet in the PDF report. See below:

If CheckBox1.Value = True Then
        PDFsheets = "Sheet11"    
End If

If CheckBox2.Value = True Then
    If PDFsheets = "" Then
        PDFsheets = "Sheet13"
    Else
        PDFsheets = PDFsheets & ",Sheet13"
    End If
End If

If CheckBox3.Value = True Then
    If PDFsheets = "" Then
        PDFsheets = "Sheet2"
    Else
        PDFsheets = PDFsheets & ",Sheet2"
    End If
End If

For example, when all 3 checkboxes are selected, MsgBox PDFsheets shows the result Sheet11, Sheet13, Sheet2

NOW, when I try to do a multiple sheet select I get Run-Time Error 9 - Subscript Out of Range. The different things I've tried include:

ThisWorkBook.Sheets(PDFsheets).Select

ThisWorkBook.Sheets(Array(PDFsheets)).Select

xPDF() = Split(PDFsheets, ",")
ThisWorkBook.Sheets(xPDF).Select

xPDF() = Split(PDFsheets, ",")
ThisWorkBook.Sheets(Array(xPDF)).Select

xPDF() = Split(PDFsheets, ",")

For i = 0 to Application.CountA(xPDF) - 1
    Sheets(xPDF(i)).Select
next i

ALSO, for the sake of brevity I have tried all of the above examples with ActiveWorkbook instead of ThisWorkBook. PLUS, I've tried rewriting my string building part to reference the sheets names instead of numbers with all of the examples above. So instead of Sheet11, Sheet13, Sheet2 the result was "Sheet11", "Sheet13, "Sheet2" with the double quotes around the sheets.

I've look at other questions and code examples on here and elsewhere that show this same goal and I'm using the selection line exactly as they have had it and I'm getting the Subscript out of range error. (I've also verified everything is spelled correctly)

Additional Note if I try typing the string value directly in it works - ThisWorkBook.Sheets("Sheet11, Sheet13, Sheet2").Select - However, this doesn't allow me to keep it variable.

Gary's Student Gary's Student · Accepted Answer · 2016-02-20T17:56:06

Your are building a comma separated string that you must convert into an array. You were very close:

Sub BuildAString()
    Dim PDFsheets As String
    Dim s As Worksheet
    PDFsheets = "Sheet1,Sheet2,Sheet3"
    ary = Split(PDFsheets, ",")

    Sheets(ary).Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\TestFolder\Book1.pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            True
End Sub

Note this makes 1 pdf file for all three sheets. You would use a loop if you wanted 3 separate files.
Note that there are no spaces inPDFsheets because my worksheet names have no spaces.

Select Multiple Excel Sheets to Export to PDF

1 Answers