VBA to Print Range of Cells to PDF

If you regularly save a range of cells in Excel to PDF using the user interface options, using VBA instead can save you time and effort.

In this tutorial, I will show you six examples of printing a range of cells in Excel to PDF using Excel VBA.

Table of Contents

Example #1: VBA to Print Range to PDF With Specified Name

Suppose you have the dataset below on the active worksheet.

You can use VBA to print the dataset to PDF with the PDF name specified in the code using the steps below:

1. On the active worksheet, press Alt + F11 to open the VB Editor.
2. Insert a new module in the VB Editor.
3. Copy the code below and paste it into the module:

Sub PrintRangeToPDFNameInCode() ' Declare variables Dim ws As Worksheet Dim file_path As String Dim print_rng As Range Dim file_name As String ' Sets the active sheet and the range to be printed Set ws = ActiveSheet Set print_rng = ws.Range("A1:D7") ' Specifies the file name and path file_name = "Partial Budget Report.pdf" ' Adding .pdf extension to the file name file_path = "C:\Annual PDF Reports\" ' Exports the specified range as a PDF to the defined file path print_rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=file_path & file_name End Sub

1. Place the cursor in the subroutine and press F5 to execute the code.

The code executes, prints the dataset to PDF, and saves the PDF file to the name specified in the code in the designated folder:

You can open the PDF file to view it:

Explanation of the Code

The code in this example prints the cell range A1:D7 on the active sheet to a PDF file named ‘Partial Budget Report’ that was specified in the subroutine. The PDF is saved in the C:\Annual PDF Reports\ folder.

You can customize the code by modifying the worksheet, range, filename, and path variables to fit your requirements.

Example #2: VBA to Print Range to PDF With Sheet Tab Name as the PDF Name

Suppose you have the dataset below on the active worksheet, ‘Budget Summary.’

You can use VBA to print the dataset to PDF with the PDF name the same as that of the active worksheet using the steps below:

1. On the active worksheet, press Alt + F11 to open the VB Editor.
2. Insert a new module in the VB Editor.
3. Copy the code below and paste it into the module:

Sub PrintRangeToPDF() ' Declares variables Dim ws As Worksheet Dim file_path As String Dim print_rng As Range ' Sets the active sheet and the range to be printed Set ws = ActiveSheet Set print_rng = ws.Range("A1:D7") ' Specifies the file path, using the worksheet name as the file name file_path = "C:\Annual PDF Reports\" & ws.Name & ".pdf" ' Exports the specified range as a PDF to the defined file path print_rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=file_path End Sub

1. Place the cursor in the subroutine and press F5 to execute the code.

The code executes, prints the dataset to PDF, and saves the PDF file to the same name as the active worksheet in the designated folder:

You can open the PDF file to view it:

Explanation of the Code

The above subroutine is designed to export a specified range (“A1:D7”) from the active worksheet to a PDF file.

The name of the PDF file is dynamically set to be the same as the name of the active worksheet, with the addition of the “.pdf” file extension. This file is saved in the “C:\Annual PDF Reports” directory. The ExportAsFixedFormat method with Type:=xlTypePDF is used for the conversion.

You can customize the code by modifying the range and path variables to fit your requirements.

Example #3: VBA to Ask User the Cell Range to Print to PDF

Suppose you have the following dataset on the active worksheet:

You can use VBA code to prompt the user for a cell range to print to PDF using the following steps:

1. On the active worksheet, press Alt + F11 to open the VB Editor.
2. Insert a new module in the VB Editor.
3. Copy the code below and paste it into the module:

Sub PrintPromptedRangeToPDF() ' Declares a variable for the range Dim PromptedRange As Range ' Prompts the user to select a range Set PromptedRange = Application.InputBox(Prompt:= _ "Choose the Specific Range", Type:=8) ' Checks if a range is selected If Not PromptedRange Is Nothing Then ' Exports the selected range as a PDF to the specified path with a given file name PromptedRange.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Annual PDF Reports\Selected Budget Report.pdf" Else MsgBox "No range selected. Operation cancelled.", vbExclamation End If End Sub

1. Place the cursor in the subroutine and press F5 to execute the code.

The code runs and presents an input box.

1. Select the range you want to print to PDF. The range reference is entered on the input box.

1. Press ‘OK’ on the input box.

The code executes, prints the selected range to PDF, and saves the file in the specified folder:

You can open the PDF file to review it:

Explanation of the Code

When you run the code in this example, an input box prompts you to choose a specific range in the active sheet. After selecting a range, the code exports that range as a PDF file with the filename “Selected Budget Report” in the specified directory.

You can customize the code by modifying the path variable to fit your requirements.

Example #4: VBA to Print Excel Range to a Timestamped PDF

Suppose you have the range ‘BudgetSummary’ below on the active worksheet.

You can use VBA to create a to export the range to a timestamped PDF file, using the steps below:

1. On the active worksheet, press Alt + F11 to open the VB Editor.
2. Insert a new module in the VB Editor.
3. Copy the code below and paste it into the module:

Sub PrintRangeToTimeStampedPDF() ActiveSheet.Range("BudgetSummary").ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Annual PDF Reports\Current Budget Report_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf" End Sub

1. Place the cursor in the subroutine and press F5 to run the code.

The code executes and exports the given range to a timestamped PDF:

Explanation of the Code

The PrintRangeToTimeStampedPDF subroutine exports a named range (“BudgetSummary“) from the active worksheet as a PDF file.

The file is saved in the “C:\Annual PDF Reports” directory with a name that includes “Current Budget Report” followed by a timestamp (formatted as “yyyymmdd_hhmmss”).

The timestamp ensures that each PDF file name is unique and reflects the exact date and time of creation. This is particularly useful for maintaining a history of reports or for version control, where each report is distinct based on the time it was generated.

Example #5: VBA to Print Multiple Ranges to PDF

Suppose you have the range ‘BudgetSummary’ on the worksheet ‘Budget Summary’ and the range ‘SalesSummary’ on the worksheet ‘Sales Summary.’

You can use VBA to print both ranges to PDF in one go using the below steps:

1. On the active worksheet, press Alt + F11 to open the VB Editor.
2. Insert a new module in the VB Editor.
3. Copy the code below and paste it into the module:

Sub PrintMultipleRangesToPDF() Dim sht1 As Worksheet, sht2 As Worksheet Dim Sheets As Collection Dim sht As Variant Set sht1 = Worksheets("Budget Summary") Set sht2 = Worksheets("Sales Summary") Set Sheets = New Collection Sheets.Add sht1 Sheets.Add sht2 For Each sht In Sheets sht.Select sht.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Annual PDF Reports\" & sht.Name Next sht End Sub

1. Place the cursor anywhere in the code and press F5 to execute the code.

The code executes, prints the two ranges to PDF, and saves the files in the specified folder:

Explanation of the Code

The code creates a new collection, adds the two worksheets, and uses a ‘For Each Next’ loop to export each worksheet to a PDF file.

You can customize the code by modifying the worksheets and path variables to fit your requirements.

Example #6: User-Defined Function to Print a Range of Cells to PDF

You can create a User-Defined Function (UDF) in VBA to print a selected range of cells to PDF. The UDF should take two arguments: first, the range of cells to be converted to PDF, and second, where the user can specify the name they want for the file.

Suppose you have the range ‘BudgetSummary’ below on the active worksheet.

Note: Cell F1 contains the name we want for the PDF file.

You can use the steps below to create a UDF and use to print a given cell range to PDF:

1. On the active worksheet, press Alt + F11 to open the VB Editor.
2. Insert a new module in the VB Editor.
3. Copy the code below and paste it into the module:

Function PRINTRANGETOPDF(SelectedRange As Range, PDFFileName As String) As Boolean On Error GoTo ErrorHandler SelectedRange.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Annual PDF Reports\" & PDFFileName PRINTRANGETOPDF = True Exit Function ErrorHandler: MsgBox "Error exporting the range to PDF: " & Err.Description, vbExclamation PRINTRANGETOPDF = False End Function

1. Select a cell near the dataset you want to print to PDF (in this example, we have entered the formula in cell F3) and enter the formula below:

=PRINTRANGETOPDF(BudgetSummary,F1)

When you press Enter, the code executes, prints the ‘BudgetSummary’ dataset to PDF, gives it the name specified in cell F1, and saves it in the designated folder.

The function returns ‘TRUE’ if the printing operation is successful.

You can open the PDF file in the specified location to view it.

Explanation of the Code

The UDF in this example allows you to export a specified range to a PDF file. If the export process is successful, it returns ‘TRUE.’ However, if an error occurs during the process, it returns ‘FALSE’ and displays the error details in a message box.

You can customize the UDF by modifying the ‘Filename’ argument of the ‘ExportAsFixedFormat’ method to fit your requirements.

In this tutorial, I showed you six examples of using VBA to print a range of cells to PDF. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• What is Excel?
• VBA to Copy Range to Email Body
• How to Separate Excel Tabs Into Separate Files
• How to Insert an Excel file into MS Word?
• Get File Names from a Folder into Excel (Copy Files Names to Excel)
• Useful Excel VBA Macro Codes Examples
• VBA to Add Border to Cells In Excel
• VBA to Copy Range to Another Sheet

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.