Excel VBA Code to Print (Save As) to PDF Multiple Pages

In this article, we will learn to print Excel sheets to PDF by Excel VBA. Here we will discuss the following examples.

Example 01: Print All Sheets of the Current Workbook to Separate PDFs by VBA

Using the following VBA code All the sheets of the current workbook will be saved in the same directory as the current workbook in PDF format

Sub Print_All_Sheets_To_Separate_PDF() 'Declare variables to store the sheet name  Dim W_Sheets As Worksheet 'Loop through all worksheets in the current workbook. For Each W_Sheets In ThisWorkbook.Worksheets 'Construct the file path for the PDF file by appending the sheet name. 'and .pdf extension to the directory path of the current workbook. Dim Path_of_file As String Path_of_file = ThisWorkbook.Path & "\" & W_Sheets.Name & ".pdf" 'Export the worksheet as a PDF file using the ExportAsFixedFormat method. 'xlTypePDF specifies the file format to export to. 'The fileName parameter specifies the path and file name of the exported PDF file.  W_Sheets.ExportAsFixedFormat _  Type:=xlTypePDF, _  Filename:=Path_of_file Next W_Sheets End Sub

Note: Before running the codes, please check the following references

Result: Suppose you have a workbook in the path containing 3 sheets.

After running the code by pressing F5 all sheets Will be Saved in pdf Format in the same folder

Example 02: Print All Sheets of the Current Workbook to Single PDF by VBA

This code will save all sheets in a single PDF with multiple pages in the same location as the current workbook.

Sub Print_All_Sheets_To_Single_PDF() ' Declare variables for file name and sheet count Dim F_Name As String Dim Sheet_count As Integer ' Set file name and sheet count values F_Name = "SheetsBundle" Sheet_count = ThisWorkbook.Sheets.Count ' Declare and set up an array to hold sheet names Dim sheet_Array() ReDim sheet_Array(Sheet_count - 1) ' this is an array of length Sheet_count For x = 1 To Sheet_count sheet_Array(x - 1) = Sheets(x).Name ' add each sheet name to the array Next x ' Select all sheets in the sheet array Sheets(sheet_Array).Select ' Export the selected sheets as a PDF file with the specified file name and save location  ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/" & F_Name & ".pdf", , , False End Sub

Result:

Example 03: Rename and Save the Selected Sheet of the Current Workbook in PDF Format in a Different Location by VBA

Sub Print_Sheet_To_PDF_And_Rename() 'Declare variables Dim F_Name As String Dim F_path As String Dim W_Sheets As Worksheet 'Input box to enter user defined PDF file name F_Name = InputBox("Please Enter the PDF Name") 'Display the Save As dialog box to let the user select a different location to save the PDF file. F_path = Application.GetSaveAsFilename(InitialFileName:=F_Name, FileFilter:="PDF Files (*.pdf), *.pdf") 'If the user cancels the Save As dialog box, exit the sub. If F_path = "False" Then Exit Sub 'Export the selected worksheet as a PDF file using the ExportAsFixedFormat method.  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _  Filename:=F_path End Sub

Result: After running the code, A user input box will appear to take the PDF name

Enter the PDF’s name and click OK.

After clicking OK, another Save As dialog box will appear. Browse your desired location to save the PDF file, then Click on the Save button

Example 04: Take Sheet Name from User Prompt and Print to PDF by VBA

Sub Print_Selected_Sheet_To_PDF() ' Declare a string variable to hold the available sheet names Dim Sheet As String Dim Sheet_Name As String ' Loop through all the worksheets in the workbook and add their names to the Sheet variable For Each Sh In ThisWorkbook.Worksheets Sheet = Sheet & Sh.Name & Chr(10) Next Sh ' Prompt the user to enter a sheet name and store the input in the x variable Sheet_Name = InputBox("Available sheets in this workbook" & Chr(10) & Sheet & Chr(10) & "Enter any sheet name") ' Select the sheet with the name entered by the user Sheets(Sheet_Name).Select ' Export the selected sheet as a PDF file with the sheet name and save location  ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/" & ActiveSheet.Name & ".pdf", , , False End Sub

Result: Run the code by pressing F5. An input box will appear to take the sheet name, which is to be printed in PDF.

Enter the sheet name and Click OK, The PDF file will be saved in the same location as the current Workbook

Example 05: Select Workbook from User Promt and Print sheet to PDF by VBA

The following VBA code will allow a user to select any workbook to print selected sheets to PDF

Sub Print_Selected_Sheet_From_Selected_Workbook_To_PDF() Dim Sheet_name As String ' Prompt the user to select a file and store the file path in the file_name variable Dim file_name As Variant file_name = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", , "Select a file", , False) ' If the user cancels the file selection prompt, exit the subroutine If file_name = False Then Exit Sub ' Open the selected workbook and activate the first sheet  Workbooks.Open (file_name) Worksheets(1).Activate ' Prompt the user to enter a sheet name and store the input in the x variable Sheet_name = InputBox("Enter the sheet name you want to print to PDF") ' Select the sheet with the name entered by the user Sheets(Sheet_name).Select ' Export the selected sheet as a PDF file with the sheet name and save location  ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Sheet_name & ".pdf", , , False ' Close the selected workbook without saving changes  ActiveWorkbook.Close savechanges:=False End Sub 

Result: Run the code by pressing F5. A file picker will open. Select the Excel file.

After clicking on the Open button, the selected Excel file will open, and another input box will ask for the name of the sheet you want to print. Enter the name and press OK.

Then the sheet will be printed in PDF format and placed in the folder of the current workbook.