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
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.
This is great – Thank you so much for showing me this. I have this exact situation – but I need to print Charts in separate excel sheets all into one pdf. I would like to have each chart fit to 1 page tall and 1 page wide regardless of where the chart is in each sheet and regardless of it’s size. Do you know how to do this? I’m new to all programming so any thoughts appreciated.
Thanks again
Thanks for this Alan.
This is terrific. I have to create sheets in a workbook for my boss.
With this code I can print a PDF document with all the sheets on it.
Many thanks
Peter
Your welcome Peter, thanks.
Hello.
I am trying out his macro and it all works however i could like the file name to be linked t a call in the excel file e.g. it should link to cell C6 instead of “Sales” like your example above.
Is there any way of doing this?
Hi Georgia,
Sure, you can adapt that bit of code as follows.
Filename:=FolderPath & “\” & Worksheets(“Kettering”).Range(“C6”)
Alan
Super
Can you export only one page (example all selected sheets second pages) to only one output PDF ?
I’m sure this would be possible using the print settings. It is not code that I have to hand though.
I’m getting a Runtime error ‘9’ Subscript out of range for the
ThisWorkbook.Sheets(Array(“Sum_instructed”, “Sum_pending”)).Select
Just check that the sheet names are correct, because that type of error indicates that it cannot find those sheets. Also, that the macro in the same workbook file that you are running it on. ThisWorkbook refers to the workbook that the macro resides in.
Hello,
how to change the order in a pdf file, how worksheets are issued?
I need the “introduction” to always be before “Sheet1”, but I always get the first page as “Sheet1” in the content of the pdf file.
I tried both without luck:
Spreadsheets (array (“introduction”, “page 1”)). Select
Spreadsheets (array (“Sheet1”, “intro”)). Select
How to change printout sequence ?
Hello
Thank you for a very great video. I was just wondering, is it possible to get the possibility to type the name of the file, instead of it is written in the code….?? “Filename:=FolderPath & “\Sales”
Best regards
Allan
Sure, the name could be picked up from a range on the sheet or maybe have an input box prompt you if you prefer.
Hi Allan,
Could you please modify the code to have it pop up for the location to save the file?
Thanks.
This can be done using application.FileDialog(msoFileDialogFolderPicker).
I show an example of using this in my VBA course – http://bit.ly/37XSKfZ
how can i do print using single assigned button for all pdf using vba code
Nice example! If I wanted to reference specific “named ranges” on multiple sheets, what adjustments should be made? For context; if sheet “Bradford” had 2 separate recaps (NamedRange_1, NamedRange_2) and sheet “Kettering” also had 2 separate recaps (NamedRange_3, NamedRange_4), how do I get “NamedRange_2” and “NamedRange_4” to save into 1 PDF file?