New VBA Subscript Out Of Range Error

[GoCavs]

Hello All,

I am trying to select sheets to generate a PDF by using a UserForm with checkboxes so the user can select which sheets he/she wants generated into a PDF. Below is my code and I feel as if I am close but I continue to get a "Run-time error '9': Subscript out of range" at the "Sheets(ary).Select" line near the bottom of my code. Any and all help is appreciated and thank you in advance for your help.

Private Sub chbxEnter_Click()


Dim PDFsheets As String
    Dim s As Worksheet
    PDFsheets = "Sheet10,Sheet15,Sheet6,Sheet5,Sheet4,Sheet14,Sheet11,Sheet12,Sheet17,Sheet13,Sheet2"
    ary = Split(PDFsheets, ",")


        If CheckBox1.Value = True Then
        PDFsheets = "Sheet10"
        End If
      
        If CheckBox2.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = "Sheet15"
        Else
            PDFsheets = PDFsheets & ",Sheet15"
        End If
        End If
            
        If CheckBox3.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = Sheet6
        Else
            PDFsheets = PDFsheets & ",Sheet6"
        End If
        End If
                  
                  
        If CheckBox4.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet4
        Else
            PDFsheets = PDFsheets & ",Sheet4"
        End If
        End If
                        
        If CheckBox5.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet5
        Else
            PDFsheets = PDFsheets & ",Sheet5"
        End If
        End If
                              
        If CheckBox6.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet14
        Else
            PDFsheets = PDFsheets & ",Sheet14"
        End If
        End If
                              
        If CheckBox7.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet11
        Else
            PDFsheets = PDFsheets & ",Sheet11"
        End If
        End If
        
        If CheckBox8.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet12
        Else
            PDFsheets = PDFsheets & ",Sheet12"
        End If
        End If
        
        If CheckBox9.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet17
        Else
            PDFsheets = PDFsheets & ",Sheet17"
        End If
        End If
        
        If CheckBox10.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet13
        Else
            PDFsheets = PDFsheets & ",Sheet13"
        End If
        End If
        
        If CheckBox11.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet2
        Else
            PDFsheets = PDFsheets & ",Sheet2"
        End If
        End If
        
  
    Sheets(ary).Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\TestFolder\Book1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
                                
                                




End Sub

 

[Scott T]

Make sure all the sheets listed in PDFsheets exist.

 

[JoeMo]

ary is a zero-based array that includes all the sheets in the string PDFSheets as you initially defined it -i.e., the line just before ary = Split(...). If one or more of those sheets doesn't exist you will get a subscript out of range error. But, I suspect you don't want all those sheets so you need to define ary after you set up the desired PDFSheets string.

 

[GoCavs]

Just double checked and all the sheets listed in the PDFSheets do exist

 

[GoCavs]

Thank you for your help, I tried to move the "ary = (Split(PDFSheets, ",")" line to below the desired PDFSheets string and I am either getting a compile error or the same subscript out of range error. Is there some place specific I should move the ary line? I'm sorry but I am extremely new to VBA.

Private Sub chbxEnter_Click()


    Dim PDFsheets As String
    Dim s As Worksheet
    PDFsheets = "Sheet10,Sheet15,Sheet6,Sheet5,Sheet4,Sheet14,Sheet11,Sheet12,Sheet17,Sheet13,Sheet3"
    ary = Split(PDFsheets, ",")
    
        If CheckBox1.Value = True Then
        PDFsheets = "Sheet10"
        End If
      
        If CheckBox2.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = "Sheet15"
        Else
            PDFsheets = PDFsheets & ",Sheet15"
        End If
        End If
            
        If CheckBox3.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = Sheet6
        Else
            PDFsheets = PDFsheets & ",Sheet6"
        End If
        End If
                  
                  
        If CheckBox4.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet4
        Else
            PDFsheets = PDFsheets & ",Sheet4"
        End If
        End If
                        
        If CheckBox5.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet5
        Else
            PDFsheets = PDFsheets & ",Sheet5"
        End If
        End If
                              
        If CheckBox6.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet14
        Else
            PDFsheets = PDFsheets & ",Sheet14"
        End If
        End If
                              
        If CheckBox7.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet11
        Else
            PDFsheets = PDFsheets & ",Sheet11"
        End If
        End If
        
        If CheckBox8.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet12
        Else
            PDFsheets = PDFsheets & ",Sheet12"
        End If
        End If
        
        If CheckBox9.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet17
        Else
            PDFsheets = PDFsheets & ",Sheet17"
        End If
        End If
        
        If CheckBox10.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet13
        Else
            PDFsheets = PDFsheets & ",Sheet13"
        End If
        End If
        
        If CheckBox11.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet3
        Else
            PDFsheets = PDFsheets & ",Sheet3"
        End If
        End If
        
    Sheets(ary).Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\TestFolder\Book1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[GoCavs]

Where exactly in the code should I put the "ary=Split(...)" line... Thank you for your help as I am very new VBA

 

[JoeMo]

Where exactly in the code should I put the "ary=Split(...)" line... Thank you for your help as I am very new VBA

After you have the desired sheets captured in the string PDFSheets, then put ary = Split(...). If you are still getting a subscript out of range error, be certain that the sheet names in the code match exactly the tab names in your workbook (no unwanted leading or trailing spaces, for example).