Export Multiple Sheets to a Single PDF File – Excel VBA

0 Flares Filament.io 0 Flares ×

This tutorial will demonstrate how to export multiple sheets of a workbook into a single PDF file using Excel VBA. The code can be seen below and adapted for your own use.

I have previously written a tutorial on exporting all the sheets of a workbook to PDF using VBA. This macro will allow you to be more specific as to what sheets are exported. It will also export to a single PDF file, rather than a separate file for each sheet.

Specifying the Sheets to be Exported

The key to the macro is to select the sheets that you want to export first. In the code below this is done with the following line.

Sheets(Array("Bradford", "Kettering")).Select

This references the sheets using their name. You could alternatively select the sheets using their index number like below.

Sheets(Array(2, 4, 5)).Select

Another alternative may be that the user selects the sheets in Excel, and then the macro is run. In this common scenario of only exporting the selected sheets, you would not need this line at all so it can be removed from the macro.

Exporting Specific Sheets to a Single PDF File – VBA Code

This VBA code can be copied and pasted into a module for the workbook. Change the code to work for your situation.

Sub ExportAsPDF()

Dim FolderPath As String

FolderPath = "C:\Users\Trainee1\Desktop\PDFs"

MkDir FolderPath
    Sheets(Array("Bradford", "Kettering")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
        openafterpublish:=False, ignoreprintareas:=False
MsgBox "All PDF's have been successfully exported."

End Sub

The ExportAsFixedFormat method is used to perform the export to PDF. The exported file has been saved as Sales in this example.

The MkDir function is used to create a new folder to save the PDF into. This creates the folder in the path specified in the macro.

If you were hoping to export sheets from multiple workbooks saved somewhere in your network, then check out this tutorial on looping through the files of a folder using VBA. By combining the code from the two macros you could export many PDF’s with one click of a button.

More awesome Excel VBA Tutorials

Online Excel VBA course

Leave a Reply

Your email address will not be published. Required fields are marked *