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:

Our example workbook has 3 sheets, and we want to save all of them together as a single PDF

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 D6, E6 and 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 D6, E6 and 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 Worksheet).

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 wksSheet1.

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.