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.
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
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
- Loop Through the Subfolders of a Directory using FileSystemObject
- Automatically Create a Table of Contents in Excel
- Using the Offset Property in VBA