This article illustrates 2 different examples to save an active sheet as a PDF with a filename that comes from a cell value using a Macro in Excel. We’ll use Excel’s built-in ExportAsFixedFormat method to configure the macro.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Examples to Save Active Sheet as PDF with Filename from Cell Value Using Macro in Excel

Introduction to the Workbook.ExportAsFixedFormat method

In Excel VBA, the ExportAsFixedFormat method allows us to save and publish a workbook in PDF format with several properties. The syntax of the methods is as follows.

expression.ExportAsFixedFormat( Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr )

Write Code in Visual Basic Editor

To save a worksheet as a PDF with a filename from cell value, we need to open and write Macro in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

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

Now put your code inside the visual code editor and press F5 to run it.

1. Use a Macro to Save and Open 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 immediately.

Solution: We need to use the ExportAsFixedFormat method in our macro and set the following arguments along with the others.

Type as xlTypePDF to save the file as PDF and
OpenAfterPublish as True to open and view the published PDF file.

In addition, we need to put a filename in cell C13 that will be extracted by the macro to name the PDF file.

Code: 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

In the above code, we’ve set the folder location as “D:\Exceldemy\” to save the PDF file.

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

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 i.e., want to filter the dataset for the Fruits category. Then save the filtered dataset as a PDF with a filename from the cell value of C13. We want to open and view the file after publishing immediately.

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)

We need to set the arguments in the Range.AutoFilter method to use in our code as follows.

Field – 3, as the 3rd column represents the category names.
Criteria1–  the cell reference of the value Fruit in Sheet1.

Code: Insert 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

Output: A PDF file named SaleDetails.pdf has been successfully saved in the specified folder location having the filtered dataset for Fruit products only.

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.

Conclusion

Now, we know how to save an active sheet as a PDF with a filename coming from a cell value using macro in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

The post Excel Macro to Save as PDF with Filename from Cell Value (2 Examples) appeared first on ExcelDemy.