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.


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 )

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.

Excel Macro Save as PDF Filename from Cell Value

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.

Excel Macro Save as PDF Filename from Cell Value

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

Excel Macro Save as PDF Filename from Cell Value

Read More: Export Excel to PDF with Hyperlinks (2 Quick Methods)


Similar Readings


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.

Excel Macro Save as PDF Filename from Cell Value

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.

Excel Macro Save as PDF Filename from Cell Value

Read More: Print Range to PDF with VBA in Excel (5 Easiest Examples)


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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

4 Comments
  1. 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:

      ActiveSheet.ExportAsFixedFormat Type:=TypePDF, filename:=filename

      Regards,
      Tanjim Reza

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

      1

      2

      3

      • 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 

      4

      Finally, you will get the PDF files in your designated folder.

      5

      Also, the PDF files will be opened automatically.

      6

      I hope these steps will give your desired results.

      Thank you
      Tanjima Hossain

Leave a reply

ExcelDemy
Logo