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]

Sub Maybe()
Dim shtArr, i As Long
shtArr = Array("Weld", "Composite", "Rubber")
Application.ScreenUpdating = False
    For i = LBound(shtArr) To UBound(shtArr)
        Sheets(shtArr(i)).Visible = xlSheetVisible
    Next i
Sheets(shtArr).Copy
With ActiveWorkbook
    .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "New_Book" & ".pdf"
    .Close False
End With
Sheets(Array("Sheet4", "Sheet5", "Sheet6")).Visible = False
Application.ScreenUpdating = True
End Sub

 

[pwb100]

Sub Maybe() Dim shtArr, i As Long shtArr = Array("Weld", "Composite", "Rubber") Application.ScreenUpdating = False For i = LBound(shtArr) To UBound(shtArr) Sheets(shtArr(i)).Visible = xlSheetVisible Next i Sheets(shtArr).Copy With ActiveWorkbook .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "New_Book" & ".pdf" .Close False End With Sheets(Array("Sheet4", "Sheet5", "Sheet6")).Visible = False Application.ScreenUpdating = True End Sub

Many thanks.

The code runs ok without any errors and I get the message 'publishing' and it appears to complete successfully but there is no pdf produced. When I ran the one from @Fluff it produced a pdf file. Unfortunately it produced one file per worksheet (from the specified range) rather than a single file for all worksheets.

 

[pwb100]

Many thanks.

The code runs ok without any errors and I get the message 'publishing' and it appears to complete successfully but there is no pdf produced. When I ran the one from @Fluff it produced a pdf file. Unfortunately it produced one file per worksheet (from the specified range) rather than a single file for all worksheets.

OK - I found the published PDF which is fantastic - thank you @jolivanes.
As the workbook is a template and data will be changed by different users, would it be possible to publish the sheets with a file name determined by a cell value?

 

[jolivanes]

Change this

.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "New_Book" & ".pdf"

to this

.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Sheets("Rubber").Range("A1").Value & ".pdf"

or whatever reference you have in mind

 

[pwb100]

Change this

.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "New_Book" & ".pdf"

to this

.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Sheets("Rubber").Range("A1").Value & ".pdf"

or whatever reference you have in mind

Perfect - thank you for all your help.

 

[jolivanes]

Thanks for the update. Very much appreciated. A lot of people don't even bother being polite anymore.
Thanks again and good luck

 

[pwb100]

OK - the code created, with the support of @jolivanes works brilliantly. On the press of a command button it will open hidden worksheets, combine them into one pdf, hide the previously hidden worksheets and open the single pdf of those worksheet.

Some of those worksheets have zero data in them. Is there a way to export only those, from the selected range, that have a number greater than zero in a single cell reference to be exported to PDF?

 

[pwb100]

OK - the code created, with the support of @jolivanes works brilliantly. On the press of a command button it will open hidden worksheets, combine them into one pdf, hide the previously hidden worksheets and open the single pdf of those worksheet.

Some of those worksheets have zero data in them. Is there a way to export only those, from the selected range, that have a number greater than zero in a single cell reference to be exported to PDF?

Or, conversely, to not export the sheets that have a zero in the reference cell. The cell reference will be the same in each of the multiple sheets.

 

[jolivanes]

Don't quote please. Just extra clutter we don't need.
Show us the code as you currently have it.