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

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.

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

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 )

Argument	Required/Optional	Explanation
*Type*	Required	xlTypePDF for the PDF format and xlTypeXPS for the XPS format.
*Filename*	Optional	A string to determine the PDF filename. We can determine the full path or Excel will save the PDF file in the current folder.
*Quality*	Optional	We can specify the quality of the spreadsheets- xlQualityStandard or xlQualityMinimum.
*IncludeDocProperties*	Optional	True or False to include the Document properties.
*IgnorePrintAreas*	Optional	Put True to ignore any print areas set while publishing and False otherwise.
*From*	Optional	The starting page number for publishing.
To	Optional	The ending page number for publishing.
*OpenAfterPublish*	Optional	Set to True to view the PDF file after publishing or False otherwise.
*FixedFormatExtClassPtr*	Optional	Pointer to the FixedFormatExt class.

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.

Read More: Excel Macro to Save as PDF

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.

Download Practice Workbook

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

Save as PDF Filename from Cell Value.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

Keith Frost Jul 16, 2022 at 9:08 PM

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
- Reply
  Tanjim Reza Aug 30, 2022 at 11:13 AM
  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:
```
ActiveSheet.ExportAsFixedFormat Type:=TypePDF, filename:=filename
```
  Regards,
  Tanjim Reza
darren Nov 18, 2022 at 5:52 PM

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:)
- Reply
  Tanjima Hossain Nov 20, 2022 at 3:34 PM
  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.
```
 Sub SaveAsPDF()
Dim filename As String
Dim ws As Worksheet
Dim mywsname As String
For Each ws In Worksheets
ws.Select
mywsname = ws.Name
filename = "D:\Exceldemy\" & mywsname
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=filename, _
Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, openafterpublish:=True
Next ws
End Sub 
```
  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