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.

Download Workbook


5 Cases to Print Range to PDF Using Excel VBA

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.

Excel VBA print range to pdf

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.

Excel VBA print range to pdf

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

declaring a range

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

Excel VBA print range to pdf

After that, a Module will be created.

declaring a range

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.

declaring a range

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

declaring a range

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

Excel VBA print range to pdf

Read More: Excel Macro to Save as PDF with Filename from Cell Value (2 Examples)


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.

Excel VBA print range to 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.

InputBox Method

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

InputBox Method

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

InputBox Method

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

Excel VBA print range to pdf

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.

Excel VBA print range to pdf

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.

creating a function

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

=range_to_pdf(B3:E13)

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

Excel VBA print range to pdf

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

creating a function

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

creating a function

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

Excel VBA print range to pdf

Read More: Excel VBA: ExportAsFixedFormat PDF with Fit to Page (3 Examples)


Similar Readings


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.

Excel VBA print range to pdf

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.

saving pdf with current date and time

➤ 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).

saving pdf with current date and time

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

saving pdf with current date and time


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.

Excel VBA print range to pdf

Excel VBA print range to 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.

Multiple sheets

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

Multiple sheets

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

Multiple sheets

and List2.pdf like these figures.

Excel VBA print range to pdf

Read More: How to Save Multiple Excel Sheets as One PDF (2 Easy Methods)


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.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo