How to Save an Active Sheet as a PDF with a Filename from Cell Value Using Macro in Excel

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.

Excel Macro Save as PDF Filename from Cell Value" width="536" height="361" />

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 Sub

We’ve set the folder location as “D:\Exceldemy\” to save the PDF file.

Excel Macro Save as PDF Filename from Cell Value" width="609" height="247" />

A PDF file named SaleDetails.pdf has been successfully saved in the specified folder location.

Excel Macro Save as PDF Filename from Cell Value" width="439" height="128" />

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.

Excel Macro Save as PDF Filename from Cell Value" width="542" height="371" />

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 Sub

A PDF file named SaleDetails.pdf has been successfully saved in the specified folder location, which contains the filtered dataset for Fruit products only.

Excel Macro Save as PDF Filename from Cell Value" width="633" height="470" />

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