Print sheets based on Check Box value

[Tanyaann1995]

Hi,

I have prepared below code to print only certain sheets in a workbook based on the ticked checkboxes in a userform. But, I'm getting a "Run Time Error 9: Subscript out of Range" in the below highlighted purple line. Please advise if there is any way to fix this error.

Function uniqueArr(ParamArray myArr() As Variant) As Variant()
    Dim dict As Object
    Dim V As Variant, W As Variant
    Dim I As Long

Set dict = CreateObject("Scripting.Dictionary")
For Each V In myArr 'loop through each myArr
    For Each W In V 'loop through the contents of each myArr
        If Not dict.exists(W) Then dict.Add W, W
    Next W
Next V
uniqueArr = dict.keys
End Function

Sub pdf()
   
    Dim PDFfile As String
    Dim CurrentPrinterNe As String, PrinterNe As String
    Dim wksAllSheets As Variant
    Dim wksSheet1 As Worksheet
    Dim wb As Workbook
    Dim arr1, arr2, arr3, arr4, arr5
   
    Set wb = ActiveWorkbook
   
UserForm5.Show
If UserForm5.CheckBox1.Value = True Then
arr1 = Array("Cover")
Else
arr1 = Array("")
End If
If UserForm5.CheckBox2.Value = True Then
arr2 = Array("Revision")
Else
arr2 = Array("")
End If
If UserForm5.CheckBox3.Value = True Then
arr3 = Array("Emerson COMMERCIAL OFFER")
Else
arr3 = Array("")
End If
If UserForm5.CheckBox4.Value = True Then
arr4 = Array("Technical Offer")
Else
arr4 = Array("")
End If

wksAllSheets = uniqueArr(arr1, arr2, arr3, arr4)

PDFfile = wb.path & "\" & wb.Worksheets(1).Range("F12").Value & ".pdf"
[COLOR=rgb(85, 57, 130)]wb.Sheets(wksAllSheets).Select[/COLOR]
Set wksSheet1 = wb.Sheets("Cover")
    wksSheet1.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=False
wb.Worksheets(1).Select

End Sub

 

[NoSparks]

One (or more) of the sheet names differ from what is being specified in the code.

 

[Tanyaann1995]

One (or more) of the sheet names differ from what is being specified in the code.

Hi,

Please can you elaborate. I'm not able to find the source of this error. Or, please can you provide a corrected code.

 

[abdelfattah]

@Tanyaann1995

Hi

actually I'm not at good in vba ,but I think you have sheet name is existed in your code doesn't match with sheet name in your file .

so I suggest to use coping the sheets names from inside the file and paste in your code. . not writing just copy & paste . somtimes the sheets names contains for spaces . that's why shows the error
I hope this help you

 

[NoSparks]

I set up a test workbook in order to test your code.
In doing so, to name the sheets the same as what you have in your code, I copied what's between the double quotes and pasted each of those as the name on the sheet tabs and your code ran without getting that error.
In order to reproduce the error you get, I inserted an extra space into one of the sheet names on the tabs.
Hence, why I say.... the sheet names differ from what is being specified in the code.

Something such as an extra space character at the beginning or end of the name showing on the sheet tab is very difficult to spot visually.
I'd suggest you do as I did and copy the names from the code to rename your sheets.

Good luck with the project
NoSparks

 

[Tanyaann1995]

I set up a test workbook in order to test your code.
In doing so, to name the sheets the same as what you have in your code, I copied what's between the double quotes and pasted each of those as the name on the sheet tabs and your code ran without getting that error.
In order to reproduce the error you get, I inserted an extra space into one of the sheet names on the tabs.
Hence, why I say.... the sheet names differ from what is being specified in the code.

Something such as an extra space character at the beginning or end of the name showing on the sheet tab is very difficult to spot visually.
I'd suggest you do as I did and copy the names from the code to rename your sheets.

Good luck with the project
NoSparks

Thanks, it worked now