Worksheet names in an array getting run time error 9

[lattechic]

Hi guys,

I have a userform where I can select the worksheets I want to print as pdf. I have saved the names in an array (SheetsToPrint). However, when I get to the line ThisWorkbook.Sheets(Array(SheetsToPrint)).Select, I get run time error 9.

Please help. Thank you in advance.

Private Sub cmdOK_click()


Dim SheetsToPrint() As Variant
Dim ws As Worksheet
Dim LastRow As Integer, i As Integer
Dim x As Integer: x = 2
Dim y As Long: y = 0
Dim printSheets As Variant


Application.ScreenUpdating = False


Set ws = Worksheets("Lookup")
LastRow = Cells(ws.Rows.Count, "R").End(xlUp).Row


'loop through each check box and save
For i = 1 To 22
            
    If Controls("CheckBox" & i).Value = True Then
        ws.Cells(x, 19) = "Y"
        y = y + 1
        ReDim Preserve SheetsToPrint(y)
        SheetsToPrint(y) = ws.Cells(x, 18)
        
    Else
        ws.Cells(x, 19) = "N"
    End If
    
    x = x + 1
    
Next i


ThisWorkbook.Sheets(Array(SheetsToPrint)).Select

ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=ActiveWorkbook.Path & "\Entry Form.pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False


Sheets("Start Express Parcels").Select


Unload Me
Application.ScreenUpdating = True




End Sub

 

[Norie]

If SheetsToPrint is an array you shouldn't need Array.

ThisWorkbook.Sheets(SheetsToPrint).Select

 

[lattechic]

Hi Norie,

Thank you for the quick reply. Cleaned up the program a bit and tried it but I'm still getting the same error code.

Private Sub cmdOK_click()


Dim SheetsToPrint() As Variant
Dim ws As Worksheet
Dim i As Integer
Dim x As Integer: x = 2
Dim y As Long: y = 0
Dim printSheets As Variant


Application.ScreenUpdating = False


Set ws = Worksheets("Lookup")


'loop through each check box and save
For i = 1 To 22
            
    If Controls("CheckBox" & i).Value = True Then
        y = y + 1
        ReDim Preserve SheetsToPrint(y)
        SheetsToPrint(y) = ws.Cells(x, 18)
    End If
    
    x = x + 1
    
Next i


ThisWorkbook.Sheets(SheetsToPrint).Select




ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=ActiveWorkbook.Path & "\Entry Form.pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False


Sheets("Start Express Parcels").Select


Unload Me
Application.ScreenUpdating = True


End Sub

 

[Fluff]

Check that your sheet names are spelt correctly, including any leading/trailing spaces.

 

[RoryA]

Unless you've got an Option Base 1 statement at the top of the module, you've got an empty item 0 in your array. You should use:

For i = 1 To 22
            
    If Controls("CheckBox" & i).Value = True Then
        ws.Cells(x, 19) = "Y"
        ReDim Preserve SheetsToPrint(y)
        SheetsToPrint(y) = ws.Cells(x, 18)
        y = y + 1
    Else
        ws.Cells(x, 19) = "N"
    End If
    
    x = x + 1
    
Next i

Note the position of the blue line.

 

[lattechic]

Names are correct.

 

[lattechic]

Thank you, RoryA! it's working now. Thanks, Norie and Fluff!