Printout only visbile sheet in Array

[jamesmcintyre]

Hello, i am trying to run a macro that print an array of sheets in the description. However i have a macro that hides certain sheets pending other options and when i do the printout option it comes up with an error if one of the ****s has been hidden. The sheets need to remain hidden depending on the options.

The code i currently have is below.

Sub PrintSpecificSheets()
ThisWorkbook.Worksheets(Array("D 30%", "EW 30%", "RW 30%", "S 30%", "W 30%")).PrintOut
End Sub

Is there an option i can include that only prints the visible sheets out of whats included in the above array?

 

[Jimmypop]

Hi there...

Try the code below...

Sub PrintSpecificSheets()
    Dim SH As Worksheet
    For Each SH In Worksheets(Array("D 30%", "EW 30%", "RW 30%", "S 30%", "W 30%"))
        If SH.Visible = xlSheetVisible Then
            SH.PrintOut Copies:=1, Collate:=True
            Exit For
        End If
    Next SH
End Sub

 

[jamesmcintyre]

Thank you, it has partly worked. Its not throwing up the error if 1 is hidden but its not printing all the visible ones still.

 

[Jimmypop]

Thanks for the feedback... Not at PC at the moment, maybe someone else can also jump in and see where it went wrong?

 

[Jimmypop]

@jamesmcintyre

Try updated...

Sub PrintSpecificSheets()
    Dim SH As Worksheet
    For Each SH In Worksheets(Array("D 30%", "EW 30%", "RW 30%", "S 30%", "W 30%"))
        If SH.Visible = xlSheetVisible Then
            SH.Select
            SH.PrintOut
        End If
    Next SH
End Sub

 

[jamesmcintyre]

Almost there, i just need to figure out how to get it to do as a combined pdf rather then all individually. I really appreciate your help.

 

[Jimmypop]

What do you mean by combined pdf? Do you want to export the selected sheets to a pdf file?

 

[jamesmcintyre]

Yes please. The idea is that only the visible sheets in the array are exported as pdf and all sheets are combined the export if possible.

 

[Jimmypop]

Try:

Sub PrintSpecificSheets()
On Error GoTo errorhandler
    Dim SH As Worksheet
    For Each SH In Worksheets(Array("D 30%", "EW 30%", "RW 30%", "S 30%", "W 30%"))
        If SH.Visible = xlSheetVisible Then
            SH.Select
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "exported file.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        End If
    Next SH
errorhandler:
    Exit Sub
End Sub

 

[jamesmcintyre]

Combined perfectly although when one of the sheets in the array isnt visible the print applies to all sheets in the workbook and not the array.