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.

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

Suppose you have the dataset below on the active worksheet. 

Dataset to convert to PDF

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:

Excel range saved as PDF using specified name

You can open the PDF file to view it:

Excel range saved as PDF result

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.

Also read: How to Convert PDF to Excel without Software?

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

Dataset to save range as PDF with sheet name as PDF name

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:

PDF saved from Excel range

You can open the PDF file to view it:

Excel range saved as PDF result

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:

Dataset to save range as PDF using VBA

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.
Input box asking user for input
  1. Press ‘OK’ on the input box.

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

VBA saved excel range as PDF

You can open the PDF file to review it:

Excel range saved as PDF result

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.

Also read: VBA Delete Files in Folder

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

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

Dataset to save range as PDF using VBA timestamped

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:

VBA range saved as 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.

Also read: How to Save Workbook Using VBA

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:

Multiple ranges saved as PDF using vba

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.

Also read: How to Save Selection in Excel as PDF?

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:

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.

Leave a Comment