Save multiple sheets to .pdf

excel vba pdf pdf-generation export-to-pdf
guthrie picture guthrie · Jan 18, 2013 · Viewed 137.2k times · Source

I have a reporting spreadsheet that grabs some data from a database and forms three sheets of summary report information. I want to run the spreadsheet automatically from the command line, and have it automatically save all three reporting sheets as a PDF file(s).

At first I thought I could have a VBA macro on the sheet do this by a series of "print as PDF", but that requires an intermediary interactive dialog box to specify the output file name. Then I find that I can just save as pdf, and the macro can set the output file name. However this creates three separate files, and I have to then later put them together externally to merge them.

(It is odd that save as pdf only saves one sheet, where other save modes seem to save the entire workbook.)

Yes, there are tools for merging the three files later, but I want to know if there is some easy way to get Excel to save multiple sheets together as one pdf file.

I print now by a series of things like: 

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ...

Could I do instead a single statement something like (pseudo-code):

ThisWorkbook.Sheets(1,2,3,4).ExportAsFixedFormat Type:=xlTypePDF, ...

Answer

Tim Williams picture Tim Williams · Jan 18, 2013

Start by selecting the sheets you want to combine:

ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\tempo.pdf", Quality:= xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=True

Related questions

Exporting an MS Excel 2003 workbook to PDF via VBA

I have an Excel 2003 workbook that contains a macro to copy certain of its sheets across to a new workbook, then save and close the new workbook. It does this several dozen times, with slightly different sheet selections each time. …

Read More
VBA Print to PDF and Save with Automatic File Name

I have a code that prints a selected area in a worksheet to PDF and allows user to select folder and input file name. There are two things I want to do though: Is there a way that the PDF …

Read More
Save Excel range as pdf

I'd like to 'save as' a specific sheet or specific range to pdf. I tried implementing a range into my code. Here is what I've been working with: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "c:\Book1.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, …

Read More