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.
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 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.
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.
=range_to_pdf(B3:E13)
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.
Read More: Excel VBA: ExportAsFixedFormat PDF with Fit to Page (3 Examples)
Similar Readings
- How to Convert Excel to PDF without Converter (5 Effective Methods)
- Print to PDF Using Macro Button in Excel (5 Macro Variants)
- How to Save Excel as PDF on One Page (3 Easy Methods)
- Export Excel to PDF with Hyperlinks (2 Quick Methods)
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: 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.
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
- How to Convert Excel to PDF with All Columns (5 Suitable Ways)
- Print to PDF and Email Using VBA in Excel (2 Useful Cases)
- How to Convert Excel to PDF without Losing Formatting (5 Effective Ways)
- Excel VBA: Create Invoice and Save PDF Format (with Quick Steps)
- How to Save Excel as PDF without Cutting Off (4 Suitable Ways)
THank you very much for this fine and easy to follow section. It helped me a great deal. God Bless You .
Dear Pirooz Rahimi,
You are most welcome and thanks for your appreciation.
Regards
ExcelDemy
Thank you very much.
I have this problem after running code:
Run-time error ‘1004’:
Document not saved. the document may be open, or an error may have been encountered when saving.
Dear KAZEM
Thank you for taking the time to read this article. I see that you are facing a problem while executing the code. It is the Run-time error ‘1004’. There are several possible reasons for this error. In your case, I believe you are having this issue because the file path you specified is incorrect or unavailable. Please note that you need to modify the file path according to the destination where you want to save the PDF file. You have to change the file path portion in the Filename variable.
For your convenience, I am attaching a photo indicating the exact portion of the code that you need to re-write.
If you have any more questions, please let us know in the comments.
Regards
Md. Abu Sina Ibne Albaruni
Team ExcelDemy
thanks
Hi Kazem,
You are welcome.
Regards
ExcelDemy