How Do I Save Multiple Sheets as a Single PDF
How do I save multiple sheets (in the same workbook) to a single output PDF?
Sometimes you need to deliver NON-Excel reports to your team, and when that’s the case PDF is probably your go to. Fortunately, VBA has made it very easy to push out combined-sheet PDFs — like this one, for example:
This file contains three
Worksheets, and we want all of them to be combined into a single PDF. For a little extra fun, we are also going to dynamically set the file name, using cells
F6. So without further ado, let’s get to the code!
Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.
Nice! Let’s walk through our script using the 4-step VBA process as tour guide:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
Per usual, we start with Step 1 – Setup, which is handled by lines 9-11. We assign
wksSheet1 to our “Sheet1”
Worksheet (there are less explicit ways to do this, but I prefer explicit over implicit and you should too). We then create a
Variant Array, which holds all the names of the
Worksheets we are targeting. Finally,
strFilepath holds the parent folder we would like to write our PDF file to.
Next up is Step 2 – Exploration, which happens on lines 14-21. We want to use cells
F6 to create our actual PDF file name (D6 E6-F6.pdf), so we collect the values from each cell and concatenate everything together using
Step 3 – Execution happens on lines 25-31. Line 24 uses
Select, which should usually be avoided, but comes in handy here as the next line relies on it. Line 25 uses the
Worksheet.ExportAsFixedFormat method, since
Selection.ExportAsFixedFormat appears to be buggy in Excel 2013! (Check this Stack Overflow post for more context on the Excel 2013 bug: http://stackoverflow.com/questions/20750854/excel-vba-to-export-selected-sheets-to-pdf)
The most important consideration here is
Filename:=, which we set to the
strFilename variable we created in Step 2 – Exploration (which uses info from our first
With that, our PDF is created!
And finally, Step 4 – Cleanup. We want to leave our user with a single active
Worksheet (rather than leaving all three of them selected), since leaving all three selected might lead to the user making mistakes. This is accomplished by line 34, which selects
Boom — you’ve got a dynamically-named PDF that contains all three sheets!
Is your PDF-generating script banging out reports like a champ? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
Get the VBA Toolbelt!
Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.