How to Print certain sheets in a workbook into PDF

Question

I want to convert certain excel sheets into PDF, the following sheets are "COVER","SCOPE","SUMMARY", "Updated Hours EST" and "RATES".

The following code below is for physically printing the certain sheets in the file. So how would you go about converting the code below into PDF print instead.

Private Sub PrintPDF_Button_Click()

     Dim WB As Workbook
     Dim arr As Variant
     Dim i As Long
     Const mySheets As String = "COVER,SCOPE,SUMMARY, Updated Hours EST, RATES"

   Set WB = ActiveWorkbook

    arr = Split(mySheets, ",")
    For i = LBound(arr) To UBound(arr)
        WB.Sheets(arr(i)).PrintOut
    Next i
End Sub

what would be the command for PDF printer because not all the users using the workbook will have PDF printer as a default choice. — GoldFusion, Nov 19 '18 at 16:44
Well then If you have a version above 2010, you could make a copy of your workbook, remove all unwanted sheets and save it directly "as pdf". — Maksim, Nov 19 '18 at 16:51
I want to automate it, not doing that everytime I update a field within one of those sheets. — GoldFusion, Nov 19 '18 at 17:07
https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf/14407986#14407986 — Tim Williams, Nov 19 '18 at 18:47

K.Dᴀᴠɪs · Accepted Answer · 2018-11-19T18:15:42.127

You can try seeing if the worksheet.ExportAsFixedFormat method will do what you need it to do:

Const savePath$ = "C:\........\"

Dim i As Long, arr()
arr = Array("COVER", "SCOPE", "SUMMARY", "Updated Hours EST", "RATES")

For i = 0 To UBound(arr)

    ThisWorkbook.Worksheets(arr(i)).ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=savePath & arr(i) & ".pdf", quality:=xlQualityStandard, _
            openafterpublish:=False

Next

Just make sure that you update savePath string to your file location - otherwise you can omit that altogether and go for something that will prompt a saveas dialog box if that is your preference.

Further reading on this method:

Worksheet.ExportAsFixedFormat method _{^{(Source: MS Docs)}}

Exports to a file of the specified format.

Syntax

expression.ExportAsFixedFormat( _Type_ , _Filename_ , _Quality_ , _IncludeDocProperties_ , _IgnorePrintAreas_ , _From_ , _To_ , _OpenAfterPublish_ ) (Definitions)

Remarks

This method also supports initializing an add-in to export a file to a fixed-format file. For example, Excel will perform file format conversion if the converters are present. The conversion is usually initiated by the user.

Thanks it worked, but at the moment the pdf is separate for each sheet and so it there is a way to combine those sheets into one big PDF file? — GoldFusion, Nov 19 '18 at 18:33
https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf/14407986#14407986 — Tim Williams, Nov 19 '18 at 18:46

How to Print certain sheets in a workbook into PDF

1 Answers1

Worksheet.ExportAsFixedFormat method (Source: MS Docs)

Syntax

Remarks

Worksheet.ExportAsFixedFormat method _{^{(Source: MS Docs)}}