Print multiple hidden worksheets but save as single file

[pwb100]

I came across the following vba code to print hidden worksheets - many thanks @Fluff . I would like to print hidden worksheets as one file (of the worksheets within the specified range) rather than an individual file per worksheet.

Sub Print_Hidden()
   Dim Sht As Worksheet
   Application.ScreenUpdating = False
   For Each Sht In Sheets(Array("Weld", "Composite", "Rubber"))
      Sht.Visible = True
      Sht.PrintOut Copies:=1, Collate:=True
      Sht.Visible = False
   Next Sht
   Application.ScreenUpdating = True
End Sub

 

[jolivanes]

So it is simply printing all the hidden sheets where the value in Cell J3 > 0 (zero) into a single PDF file?
And please again, don't create more clutter then we already have. No need quoting a post just above it (Post #19 quoted in Post #29!!!!!). If you need to refer to a post, use the post number.

Just to make things as easy as possible, are there any hidden sheets that are not part of this checking?

 

[pwb100]

Ahh sorry - I just pressed the reply button at the bottom of your last post and it automatically added the quote from it. I will delete in future.

To answer your question - yes there are other hidden sheets that are not part of the checking. They are sheets that I hide to prevent staff tampering with things. It is only those sheets, F1-F33, that need to be checked and exported.

 

[jolivanes]

I've gone way overboard here but it should, I hope, solve all the problems previously encountered.
The saveNm line is where the name for the PDF file to be saved is. Change both, sheet name and cell address, as needed.
Code will unhide all the hidden worksheets and put their names in an array.
It'll then check if the Value of these sheets in cell J3 is more than 0 (zero)
If so, these sheet names are put into a string and later converted to an array
These sheets are then saved as a PDF file and names as saveNm
Finally, the sheets are hidden again.

Sub SavePDF_New_3()
Dim saveNm As String, sh As Worksheet, i As Long, j As Long, shtArr1, shtArr2
saveNm = Sheets("Sheet1 (F2)").Range("A3").Value   '<---- Change sheet name and range as required.
Application.ScreenUpdating = False

For Each sh In ThisWorkbook.Worksheets
    If sh.Visible = False Then
        sh.Visible = True
        shtArr1 = shtArr1 & "|" & sh.Name
    End If
Next sh
shtArr1 = Split(Mid(shtArr1, 2), "|")

For i = LBound(shtArr1) To UBound(shtArr1)
    If Left(shtArr1(i), 9) = "Sheet1 (F" Then
        If Sheets(shtArr1(i)).Range("J3").Value > 0 Then shtArr2 = shtArr2 & "|" & shtArr1(i)
    End If
Next i
shtArr2 = Split(Mid(shtArr2, 2), "|")

Sheets(shtArr2).Copy
    With ActiveWorkbook
        .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & saveNm & ".pdf", OpenAfterPublish:=True
        .Close False
    End With
    
For j = LBound(shtArr1) To UBound(shtArr1)
    Sheets(shtArr1(j)).Visible = False
Next j
Application.ScreenUpdating = True

End Sub

 

[pwb100]

Many, many thanks @jolivanes for all your support with this. The sheets now do exactly as I had visioned. Your help is very much appreciated.

 

[jolivanes]

Thanks for letting us know and good luck.