Print Range to PDF with VBA in Excel (5 Easiest Examples)

If you are looking for some of the easiest ways to print range to PDF using Excel VBA, then you will find this article useful. So, let’s start our main article.

Print Range to PDF Using Excel VBA: 5 Cases

Here, we have the following dataset containing the sales records of different products of a company. Throughout this article, we will print this data range with different VBA codes easily.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Case-1: Declaring a Range to Print Range to PDF with a Cell Value as PDF Name

Here, we will print the following data range by declaring this range, and then we will save the PDF named after the value in cell F3.

Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a Module will be created.

Step-02:
➤ Write the following code

Sub range_to_pdf_1()
ActiveSheet.Range("B3:F13").ExportAsFixedFormat Type:=0, _
Filename:="C:\Users\Mima\Downloads\" & ActiveSheet.Range("F3").Value, _
Quality:=0, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
openafterpublish:=True
End Sub

Here, we have declared the range B3:F13 of the active sheet to save as a PDF.
To print this range we are using the ExportAsFixedFormat method. Then, we have put 0 as the Type for printing this range as PDF and as a Filename, we have declared the path with the name (value from cell F3) of this pdf.
Moreover, the further properties  Quality:=0 for standard quality, IncludeDocProperties:=False for ignoring the document properties, IgnorePrintAreas:=False for using the print areas, and finally to open the file after creation openafterpublish:=True is used.

➤ Press F5.
Then, you will have the PDF file in your defined location for saving this file.

After opening this file, we will have the following range as PDF.

Read More: Excel VBA: ExportAsFixedFormat PDF with Fit to Page

Case-2: Using InputBox Method to Print Range to PDF with Excel VBA

In this section, we will select our desired range after running the code instead of predefining the range in the code for printing that range as a PDF.

Steps:
➤ Follow Step-01 of Case-1.

Sub range_to_pdf_2()
Dim defined_rng As Range
Set defined_rng = Application.InputBox(Prompt:= _
"Choose the Specific Range", Title:="Microsoft Excel", Type:=8)
defined_rng.ExportAsFixedFormat Type:=0, _
Filename:="C:\Users\Mima\Downloads\" & "PDF", _
Quality:=0, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
openafterpublish:=True
End Sub

Here, we have declared defined_rng as Range and then set it with the range which we will select after the appearance of the Input Box, and for this Input Box, we are using the InputBox method.
To print this range we are using the ExportAsFixedFormat method. Then, we have put 0 as the Type for printing this range as PDF and as a Filename, we have declared the path and set the file name PDF.
In addition to this, we are using the further properties Quality:=0 for standard quality, and IncludeDocProperties:=False for ignoring the document properties. IgnorePrintAreas:=False for using the print areas, and openafterpublish:=True to open the file after creation.

➤ Press F5.
➤ Select the range which you want to print and press OK in the Input Box.

Finally, we are having the printed PDF file in our defined location.

When you will open the printed file named PDF then you will see the following range in PDF format.

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

Case-3: Using Function to Print Range to PDF

Here, we will create a function with the help of a VBA code, using this function we will select the range to print this range as PDF and this function will give us a boolean value in the Print column.

Steps:
➤ Follow Step-01 of Case-1.

Function range_to_pdf(defined_rng As Range) As Boolean
defined_rng.ExportAsFixedFormat Type:=0, _
Filename:="C:\Users\Mima\Downloads\" & "PDF", _
Quality:=0, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
openafterpublish:=True
End Function

Here, we have declared the function range_to_pdf as Boolean and the function takes an input defined_rng as Range.
For printing this input range, we are using the ExportAsFixedFormat method. Then, we have put 0 as the Type for printing this range as PDF and as a Filename, we have declared the path and set the file name PDF.
Furthermore, the properties Quality:=0 for standard quality, and IncludeDocProperties:=False for ignoring the document properties. IgnorePrintAreas:=False for using the print areas, and openafterpublish:=True to open the file after creation is used.

After saving the code, return to the main sheet.
➤ Type the following formula in the first cell of the Print column.

Here, range_to_pdf is our created function and B3:E13 is the range to print it as PDF.

➤ Press ENTER.
Then, you will get the boolean result in cell F4.        

Besides, we will have our printed range PDF file in our desired location as a result of this function.

You will have the printed PDF file like the following figure containing our selected range.

Case-4: Print Range to Pdf with Name Containing Current Date and Time

Suppose, you want to save the PDF file with the current date and time during the creation of this file and to do this you can follow this section.

Steps:
➤ Follow Step-01 of Case-1.

Sub range_to_pdf_4()
ActiveSheet.Range("B3:F13").ExportAsFixedFormat Type:=0, _
Filename:="C:\Users\Mima\Downloads\" & "PDF" & "_" & Format(Now(), _
"yyyymmdd hhmmss"), Quality:=0, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, openafterpublish:=True
End Sub

Here, we have declared the range B3:F13 of the active sheet to save as a PDF.
To print this range we are using the ExportAsFixedFormat method. Then, we have put 0 as the Type for printing this range as PDF and as a Filename, we have declared the path.
And for the file name with the name PDF, we are using the combination of the FORMAT and NOW functions to add the date and time of that moment while printing this pdf.
Moreover, the further properties  Quality:=0 for standard quality, IncludeDocProperties:=False for ignoring the document properties, IgnorePrintAreas:=False for using the print areas, and finally to open the file after creation openafterpublish:=True is used.

➤ Press F5.
Then, we will have the PDF file in our defined location with the name PDF_20220508 094859.pdf containing the present date (2022/05/08 as yyyy/mm/dd) and time (09:48:59 as hh:mm: ss).

The saved PDF contains the following range as the following figure.

Case-5: Print Range to Pdf for Multiple Sheets with Excel VBA

Here, we have the following two sheets; List1 and List2, using a code we will print all of the sheets at once as PDF.

Steps:
➤ Follow Step-01 of Case-1.

Sub range_to_pdf_5()
Dim sht1, sht2 As Worksheet
Dim combined_sheets
Set sht1 = Worksheets("List1")
Set sht2 = Worksheets("List2")
combined_sheets = Array(sht1, sht2)
For Each sht In combined_sheets
sht.Select
sht.ExportAsFixedFormat Type:=0, _
Filename:="C:\Users\Mima\Downloads\" & sht.Name, Quality:=0, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, openafterpublish:=True
Next sht
End Sub

Here, we have declared sht1, and sht2 as Worksheet, and combined_sheets is considered as a variant. Then, we set sht1 to the worksheet List1, sht2 to the worksheet List2, and combined_sheets is assigned to an array of these two sheets.
To go through each sheet of this array we are using the FOR loop, and in this loop, each sheet will be activated by selecting them, and then for printing, we have used the ExportAsFixedFormat method.

➤ Press F5.
Then, we can see the two pdf files after the sheet names of the two different sheet has been saved in our defined location.

We will have the ranges of those two sheets in different two files; List1.pdf

and List2.pdf like these figures.

Read More: Print Multiple Excel Sheets to Single PDF File with VBA

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to cover the ways to print range to PDF using Excel VBA. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• Print to PDF Using Macro Button in Excel
• How to Print to PDF in Excel VBA : With Examples and Illustrations