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

 

[pwb100]

This is the code that I currently have:

Sub SavePDF()

Dim shtArr, i As Long

shtArr = Array("Sheet1 (F1)", "Sheet1 (F2)", "Sheet1 (F3)", "Sheet1 (F4)", "Sheet1 (F5)", "Sheet1 (F6)", "Sheet1 (F7)", "Sheet1 (F8)", "Sheet1 (F9)", "Sheet1 (F10)", "Sheet1 (F11)", "Sheet1 (F12)", "Sheet1 (F13)", "Sheet1 (F14)", "Sheet1 (F15)", "Sheet1 (F16)", "Sheet1 (F17)", "Sheet1 (F18)", "Sheet1 (F19)", "Sheet1 (F20)", "Sheet1 (F21)", "Sheet1 (F22)", "Sheet1 (F23)", "Sheet1 (F24)", "Sheet1 (F25)", "Sheet1 (F26)", "Sheet1 (F27)", "Sheet1 (F28)", "Sheet1 (F29)", "Sheet1 (F30)", "Sheet1 (F31)", "Sheet1 (F32)", "Sheet1 (F33)")

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 & "\" & Sheet3.Range("B2").Value & ".pdf", OpenAfterPublish:=True

.Close False

End With

Sheets(Array("Sheet1 (F1)", "Sheet1 (F2)", "Sheet1 (F3)", "Sheet1 (F4)", "Sheet1 (F5)", "Sheet1 (F6)", "Sheet1 (F7)", "Sheet1 (F8)", "Sheet1 (F9)", "Sheet1 (F10)", "Sheet1 (F11)", "Sheet1 (F12)", "Sheet1 (F13)", "Sheet1 (F14)", "Sheet1 (F15)", "Sheet1 (F16)", "Sheet1 (F17)", "Sheet1 (F18)", "Sheet1 (F19)", "Sheet1 (F20)", "Sheet1 (F21)", "Sheet1 (F22)", "Sheet1 (F23)", "Sheet1 (F24)", "Sheet1 (F25)", "Sheet1 (F26)", "Sheet1 (F27)", "Sheet1 (F28)", "Sheet1 (F29)", "Sheet1 (F30)", "Sheet1 (F31)", "Sheet1 (F32)", "Sheet1 (F33)")).Visible = False

Application.ScreenUpdating = True

End Sub

One or more of those sheets will have zero data in them and would therefore not need exporting as a pdf.

 

[jolivanes]

Try so and let us know.
Change references like sheet names as required.

Sub SavePDF_New()
Dim shtArr, newshtArr, i As Long
shtArr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet14", "Sheet15")
Application.ScreenUpdating = False
    For i = LBound(shtArr) To UBound(shtArr)
        If Sheets(shtArr(i)).Visible = False And WorksheetFunction.CountA(Sheets(shtArr(i)).Cells) <> 0 Then
            newshtArr = newshtArr & "|" & Sheets(shtArr(i)).Name
            Sheets(shtArr(i)).Visible = xlSheetVisible
        End If
    Next i
    newshtArr = Split(Mid(newshtArr, 2), "|")
    Sheets(newshtArr).Copy
    With ActiveWorkbook
        .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Sheet3.Range("B2").Value & ".pdf", OpenAfterPublish:=True
        .Close False
    End With
Sheets(newshtArr).Visible = False
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

If your hidden sheets that need to be checked/printed to PDF are named continuously (F1, F2, F3 etc) like in your code you supplied in Post #11,
you don't need to have an array with so many items in it. That would only ask for spelling mistakes when typing the names in.
In the "ExportAsFixedFormat" line, you might need to change the "Sheets("Sheet1 (F2)")" to the sheet that has the value for the save name.

Sub SavePDF_New_2()
Dim lastSht As Long, shtArr, i As Long
lastSht = Application.InputBox("Highest Value In ""F"" designation", "Last sheet to be checked for printing.", , , , , 1)
Application.ScreenUpdating = False
    For i = 1 To lastSht    '<---- If the F designation starts at 1
        If Sheets("Sheet1 (F" & i & ")").Visible = False And WorksheetFunction.CountA(Sheets("Sheet1 (F" & i & ")").Cells) <> 0 Then
            shtArr = shtArr & "|" & Sheets("Sheet1 (F" & i & ")").Name
            Sheets("Sheet1 (F" & i & ")").Visible = xlSheetVisible
        End If
    Next i
    shtArr = Split(Mid(shtArr, 2), "|")
    Sheets(shtArr).Copy
    With ActiveWorkbook
        .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Sheets("Sheet1 (F2)").Range("B2").Value & ".pdf", OpenAfterPublish:=True
        .Close False
    End With
Sheets(shtArr).Visible = False
Application.ScreenUpdating = True
End Sub

 

[pwb100]

Many thanks, once again, for your help. I tried the code that you suggested and it gives a runtime error 9 message - subscript out of range. On debugging it gives the following:

Sub SavePDF_New()
Dim shtArr, newshtArr, i As Long
shtArr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet14", "Sheet15")
Application.ScreenUpdating = False
    For i = LBound(shtArr) To UBound(shtArr)
     If Sheets(shtArr(i)).Visible = False And WorksheetFunction.CountA(Sheets(shtArr(i)).Cells) <> 0 Then
            newshtArr = newshtArr & "|" & Sheets(shtArr(i)).Name
            Sheets(shtArr(i)).Visible = xlSheetVisible
        End If
    Next i
    newshtArr = Split(Mid(newshtArr, 2), "|")
    Sheets(newshtArr).Copy
    With ActiveWorkbook
        .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Sheet3.Range("B2").Value & ".pdf", OpenAfterPublish:=True
        .Close False
    End With
Sheets(newshtArr).Visible = False
Application.ScreenUpdating = True
End Sub

The error line is shown in green. (Apologies - i see colour doesn't show. It is line 6 of the code that gives the error)

Would it help if the it were looking for an empty cell to ignore - J3 for example. This cell will either have a number in it, and therefore requires exporting, or won't and does not therefore require exporting.

 

[jolivanes]

Did you change the sheet names in the shtArr? In the code in the above post they are not changed.

 

[pwb100]

Did you change the sheet names in the shtArr? In the code in the above post they are not changed.

I have done that now and used the following code. I tried this on a working sheet. It still exports all sheets. The sheets exported will have some data in cells as they are basically a template that picks up data from another sheet to fill certain cells. If cell J3 has no data in it then it should not be exported.

I apologise if my explanations are not very clear.

Sub SavePDF_New()
Dim shtArr, newshtArr, i As Long
shtArr = Array("Sheet1 (F1)", "Sheet1 (F2)", "Sheet1 (F3)", "Sheet1 (F4)", "Sheet1 (F5)", "Sheet1 (F6)", "Sheet1 (F7)", "Sheet1 (F8)", "Sheet1 (F9)", "Sheet1 (F10)", "Sheet1 (F11)", "Sheet1 (F12)", "Sheet1 (F13)", "Sheet1 (F14)", "Sheet1 (F15)", "Sheet1 (F16)", "Sheet1 (F17)", "Sheet1 (F18)", "Sheet1 (F19)", "Sheet1 (F20)", "Sheet1 (F21)", "Sheet1 (F22)", "Sheet1 (F23)", "Sheet1 (F24)", "Sheet1 (F25)", "Sheet1 (F26)", "Sheet1 (F27)", "Sheet1 (F28)", "Sheet1 (F29)", "Sheet1 (F30)", "Sheet1 (F31)", "Sheet1 (F32)", "Sheet1 (F33)")
Application.ScreenUpdating = False
    For i = LBound(shtArr) To UBound(shtArr)
        If Sheets(shtArr(i)).Visible = False And WorksheetFunction.CountA(Sheets(shtArr(i)).Cells) <> 0 Then
            newshtArr = newshtArr & "|" & Sheets(shtArr(i)).Name
            Sheets(shtArr(i)).Visible = xlSheetVisible
        End If
    Next i
    newshtArr = Split(Mid(newshtArr, 2), "|")
    Sheets(newshtArr).Copy
    With ActiveWorkbook
        .ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Sheet3.Range("B2").Value & ".pdf", OpenAfterPublish:=True
        .Close False
    End With
Sheets(newshtArr).Visible = False
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

"One or more of those sheets will have zero data in them" and "cell J3 has no data" are two total different things.
Change this

If Sheets(shtArr(i)).Visible = False And WorksheetFunction.CountA(Sheets(shtArr(i)).Cells) <> 0 Then

to this

If Sheets(shtArr(i)).Visible = False And Len(Sheets(shtArr(i)).Range(J3")) = 0 Then

Be aware that a cell might look empty but even a space makes it to be non empty.
I don't know what a normal value would be in J3 but you might want to run a macro that makes that cell empty if for instance the length of the value or text is less the 2 characters.
Any way. let us know how you make out.

 

[pwb100]

With the change you suggested I now get a 'Run time error 13 Type mismatch message generated. On the debugging screen it is the following line that is highlighted:

Sheets(newshtArr).Copy

The workbook I am testing this on has a numerical value in cell J3 in 14 of the 33 sheets in the array range. The others have a "0" in cell J3. This number is brought in by a VLOOKUP formula in cell J3

 

[jolivanes]

Go over everything what you have and what you want again because we keep on dancing around with misinformation.
It starts with empty sheets followed by that these sheets are not really empty, just cell J3 "has no data" followed by that J3 has a formula, which we were never made aware of, that gives a 0 (zero) in certain circumstances.
Explain in a concise manner with all information being right. As mentioned above, a formula result of 0 (zero) does never constitute a cell being empty or not having data.

 

[pwb100]

Go over everything what you have and what you want again because we keep on dancing around with misinformation.
It starts with empty sheets followed by that these sheets are not really empty, just cell J3 "has no data" followed by that J3 has a formula, which we were never made aware of, that gives a 0 (zero) in certain circumstances.
Explain in a concise manner with all information being right. As mentioned above, a formula result of 0 (zero) does never constitute a cell being empty or not having data.

I take your point and apologise for my poor explanations.

The workbook contains test scores for students. The results of the test, with further information is sent to a 'feedback sheet' for each student - sheets F1 - F33 (the array). If a student did not sit the test the feedback sheet will contain the same information as all the other sheets ( a template if you like) but a cell (J3) will have a score of 0 in it (there will be other cells without information also). This 0 figure is picked up from a VLOOKUP formula in cell J3 from the results entry sheet. When all of the results have been entered I would like all of the sheets that have a value greater than zero in cell J3, from the array to be exported as a PDF. The current code that you helped me with back in May automatically opened the published PDF sheets in a browser (this is already a vast improvement from manually selecting sheets and exporting) from where they could be printed but staff would have to edit the print range to avoid printing sheets for those students that didn't carry out the assessment.

I hope this is a better explanation. I really do appreciate all of your help, support and guidance in trying to make the process of printing these feedback sheets as simple as possible for staff.