Writing Code in Visual Basic Editor
Steps:
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.
Enter your code inside the visual code editor and press F5 to run it.
Method 1 – Using a Macro to Save and Open an Active Sheet as PDF with Filename from a Cell Value
Task: Use a macro to save and publish the following sale details as a PDF with a filename from the cell value of C13. We want to open and view the file after publishing.
Solution: We need to use the ExportAsFixedFormat method in our macro and set the following arguments along with the others.
- Enter as xlTypePDF to save the file as PDF, and
OpenAfterPublish as True to open and view the published PDF file. - Put a filename in cell C13 that will be extracted by the macro to name the PDF file.
- Insert the following code in the visual basic editor and press F5 to run it:
Sub SaveAsPDF()
Dim filename As String
filename = "D:\Exceldemy\" & Range("C13").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
filename, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End SubWe’ve set the folder location as “D:\Exceldemy\” to save the PDF file.
A PDF file named SaleDetails.pdf has been successfully saved in the specified folder location.
Read More: Excel Macro to Save as PDF
Method 2 – Filter Data Based on a Cell Value and Save the Filtered Dataset as PDF with Filename from a Cell Value
Task: To filter the dataset based on a cell value in cell C14, we want to filter the dataset for the Fruits category. Save the filtered dataset as a PDF with a filename from the cell value of C13. Open and view the file after publishing.
Solution: In the following example, we’ll use the Range.AutoFilter method in our VBA code to filter a dataset using the AutoFilter. The method has several arguments to operate with. The syntax is:
expression.AutoFilter(Field, Criteria 1, Operator, Criteria 2, SubField, VisibleDropDown)
- Set the arguments in the Range.AutoFilter method to use in our code is as follows.
Field – 3, as the 3rd column represents the category names.
Criteria1– the cell reference of the value Fruit in Sheet1.
- Enter the following code in the Visual Basic editor and press F5 to run it.
Option Explicit
Sub SaveAsPDF()
Dim category As Range
Dim filename As String
With Worksheets("Sheet1")
Set category = .Range("C14")
End With
With Worksheets("Sheet1")
With .Range("B4:G11")
.AutoFilter Field:=3, Criteria1:=category, VisibleDropDown:=True
End With
End With
filename = "D:\Exceldemy\" & Range("C13").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
filename, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End SubA PDF file named SaleDetails.pdf has been successfully saved in the specified folder location, which contains the filtered dataset for Fruit products only.
Read More: Excel VBA: Choose Location and Save as PDF
Things to Remember
- The AutoFilter method allows us to filter a dataset with a lot of flexibility. The xlAutoFilterOperator had different options to set a filter with multiple criteria.
- We used the With…End With statement to run a repetitive task in our code.
Download the Practice Workbook
Download this workbook to practice.
Conclusion
Now, we know how to save an active sheet as a PDF with a filename coming from a cell value using a macro in Excel with the help of suitable examples. Hopefully, it will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- Excel VBA: Create Invoice and Save PDF Format (with Quick Steps)
- Excel Macro: Save as PDF with Date in Filename
- Excel VBA Macro to Save PDF in Specific Folder
I have utilized dozens of iterations of code based on multiple tutorials. My intent is to save the active sheet in a workbook (an invoice) as a PDF on a MAC OS desktop with the filename of the PDF to be the invoice number which is found is cell F4 of the invoice. Regardless of how I go about it, the filename of the PDF ends up as the name of the workbook. Can you please advise?
Sub SaveAsPDF()
Dim wks As Worksheet
Set wks = ActiveSheet
Dim Path As String
Path = “Users\keithfrost\Desktop\”
Dim filename As String
filename = ActiveSheet.Range(“F4”).Value
ActiveSheet.ExportAsFixedFormat Type:=TypePDF
End Sub
Hello, KEITH FROST!
Thank you for your query.
There is no error in your code. You just need to add “filename:=filename” at the ActiveSheet.ExportAsFixedFormat command.
So, just write the export format command line as:
Regards,
Tanjim Reza
HI, continuing from Example 1
I attached the SaveasPDF() to macro button
assuming the report is for April data and i got May, June ..etc
how to auto loop thru creating May, June PDF report..?
TQ:)
Hello DARREN,
Hope you are doing well. So, to solve your issue you can follow the stated procedures below.
Here, we have the following three sheets- April, May, June, etc. Using a VBA code, we will print all these sheets into PDF format separately.
• Type the following code in your Visual Basic Editor window.
Finally, you will get the PDF files in your designated folder.
Also, the PDF files will be opened automatically.
I hope these steps will give your desired results.
Thank you
Tanjima Hossain