VBA Excel export specified range of sheets to PDF

Question

I have a few sheets located at the end, which I don't want to include in my PDF print.

My code works but looks quite tough...

 Sub DPPtoPDF()

 Sheets("Readme").Visible = False
 Sheets("Asbuilt Photos 1").Visible = False
 Sheets("Asbuilt Photos 2").Visible = False
 Sheets("Splicing Photos").Visible = False
 Sheets("Sign Off Sheet").Visible = False
 Sheets("OTDR TRACE-1").Visible = False

 ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True

 Sheets("Readme").Visible = True
 Sheets("Asbuilt Photos 1").Visible = True
 Sheets("Asbuilt Photos 2").Visible = True
 Sheets("Splicing Photos").Visible = True
 Sheets("Sign Off Sheet").Visible = True
 Sheets("OTDR TRACE-1").Visible = True
 Sheets("Frontsheet").Select


 End Sub

Basically I want to define some range of sheets, which I want to include in my PDF print.

Unfortunately, according to the thread below:

Specify an Excel range across sheets in VBA

it looks like I can set the range within one sheet only.

Other approaches:

vba print pdf files for specific list of sheets

Excel VBA Macro to save series of worksheets to one pdf then repeat for different series of worksheets

don't work in my case

Is there any smarter option to select some range of sheets /or exclude some sheets/ for PDF printing?

Create a list of worksheets and a list of their associated ranges (if they are not all the same). Loop through the lists and copy the ranges appropriately to another (new) worksheet and export it to PDF. You have specified a list of worksheets not to include. Now do so with the worksheets and their associated ranges, or with one range address, , if it's always the same, and elaborate on the matters of headers, cell formatting, empty rows,... etc. — VBasic2008

CDP1802 CDP1802 · Accepted Answer · 2021-03-24T13:56:21

Add a temporary sheet to hold all the ranges you want to print.

Option Explicit

Sub DPPtoPDF()

    'Define ranges to print out
    Dim wb As Workbook
    Dim wsPDF As Worksheet, ws As Worksheet
    Dim rngPDF As Range
    Dim n As Long, i As Integer
   
    ' define ranges
    Dim rngToPrint(2) As Range
    Set wb = ThisWorkbook
    Set rngToPrint(1) = wb.Sheets("Sheet1").Range("A1:E5")
    Set rngToPrint(2) = wb.Sheets("Sheet2").Range("C3:F200")

    Set wb = ThisWorkbook
    ' create sheet to hold ranges
    Set wsPDF = wb.Sheets.Add
    wsPDF.Name = "PDF Output"
    Set rngPDF = wsPDF.Range("A1")
  
    ' copy range to temporary sheet
    For i = 1 To UBound(rngToPrint)
        n = rngToPrint(i).Rows.Count
        rngToPrint(i).Copy rngPDF
        Set rngPDF = rngPDF.Offset(n + 1)
    Next

    ' print to PDF
    wsPDF.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=True

    ' delete temporary sheet
    Application.DisplayAlerts = False
    wsPDF.Delete
    Application.DisplayAlerts = True

 End Sub

VBA Excel export specified range of sheets to PDF

1 Answers