Print visible sheets to PDF, but leave out Sheet2 and Sheet3

[andyc6610]

I'm trying to get visible tabs to save to 1 PDF, but there are a few tabs I want to exclude from the print.

I have got some code so far but it has a few problems:

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible = True Then sht.ExportAsFixedFormat Type:=xlTypePDF,

Problems:

1. It saves each sheet as a separate PDF, rather than as a combined PDF
2. It saves all visible sheets - there are some sheets I don't want to include.
   • i.e. I always want to exclude Sheet2 and Sheet3 from the print, even though they are visible in the workbook. The rest should be included if visible (approx 100 tabs so not practical to list them all out)

Any advice??

 

[Micron]

To eliminate sheets you would exclude the sheet names. I have never tried to output several sheets at once, but maybe:

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible = True And sht.Name <> "Sheet2" And sht.Name <> "Sheet3" Then
       sht.Select
       ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,

 

[andyc6610]

Not having success with this yet. Tried it with just excluding one sheet, but still running them off as individual PDFs and not excluding the specified tabs!

 

[Micron]

Have never done this so best I can do at the moment is show what I found.

Export Multiple Sheets to a Single PDF - Excel VBA | Computergaga

This tutorial will show you how to export multiple sheets of a workbook into a single PDF using Excel VBA. The code can be adapted.

www.computergaga.com

I guess you need to build a sheet array, which I overlooked. If you don't have too many sheets you could hard code them, otherwise you'll need to build a dynamic array as shown.

Create an Array of Sheets in VBA – Excel Tutorial

HTH

 

[Micron]

I have 14 sheets in a wb and only wanted to select 2 to test with. See if you can make this work by going back to not using sheet2 or sheet3. I'll post what worked for me.

Sub testPdfLoop()
Dim ary()
Dim sht As Worksheet

ary = Array()
For Each sht In ActiveWorkbook.Worksheets
     If sht.Visible = True And sht.Name = "001" Or sht.Name = "002" Then
     'perhaps change above line to
     'If sht.Visible = True And sht.Name <> "Sheet2" And sht.Name <> "Sheet3" Then
          ReDim Preserve ary(UBound(ary) + 1)
          ary(UBound(ary)) = sht.Name
     End If
Next

ActiveWorkbook.Sheets(ary).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
      
End Sub

You might need more parameters for the export, such as the folder to save in.

 

[andyc6610]

Thank you, I've been able to tweak it to work without needing to use an array.

    Dim mySheet As Object
    For Each mySheet In Sheets
        With mySheet
            If .Visible = True _
            And .Name <> "Sheet2" _
            And .Name <> "Sheet3" _
            Then .Select Replace:=False
        End With
        
    Next mySheet
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Path & "\" & "File Name" & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

This successfully selects all visible sheets (apart from Sheet2 and Sheet3) and exports them to one PDF, saving them in the same location as the source document!

Thanks for the help!