How to Save Excel Macro Files as Filename from Cell Value

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to convert a macro file into other file types like PDF, XLSX, etc. We can convert a macro file into these types of files in many ways. But if we want to save Excel macro files as filename from cell value, it might be a little bit tricky. This tutorial will teach you how to save Excel macro files as filename from cell value in 2 suitable ways. Let’s get started!


How to Save Excel Macro Files as Filename from Cell Value: 2 Suitable Ways

We can save Excel macro files as filename from cell value using VBA codes. In this article, we will discuss 2 suitable ways to do the task. In the first method, we will use a simple VBA code and save the macro file as filename from cell value by converting it into an XLSX file in another folder. And in the other method, we will filter our dataset according to a category first and then save the file as filename from the cell value in XLSM and PDF form. To demonstrate the examples, we have taken the following dataset where we have a Sales history of some products in a shop.

excel macro save as filename from cell


1. Use of VBA Code

We will use a simple VBA code in this method to save the macro file as filename from cell value. As a result, it will be converted into an XLSX file and will be saved in C drive to our defined folder. In order to do so, follow the steps below.

Steps:

  • First, go to the Developer tab and click on Visual Basic to open the Microsoft Visual Basic editor.

Use of VBA Code to save excel macro file as filename from cell

  • Now, click on the Insert tab and select Module as shown below.

Use of VBA Code to save excel macro file as filename from cell

  • After that, copy and paste the following VBA code into your module as shown below.
Sub File_Name_As_Cell_Value()
Dim File_Name As String
Dim Destination As String
Application.DisplayAlerts = False
Destination = "C:\ExcelDemy\"
File_Name = Range("C13").Value & ".xlsx"
ActiveWorkbook.SaveAs Destination & File_Name, xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

Use of VBA Code to save excel macro file as filename from cell

  • Furthermore, click on the Run option or press F5 to run the code.

Use of VBA Code to save excel macro file as filename from cell

  • As a result, if you go to C drive and open the ExcelDemy folder, you will see an XLSX file named after the cell value (SaleDetails) of your dataset.

Read More: How to Use Macro to Save Excel File with New Name


2. Filter Data Based on a Cell Value

In this method, we will filter our dataset according to a category and then save the macro file in XLSM and PDF form as filename from cell value in our specified folder.


2.1 Save as XLSM File

The first one is to filter our dataset and save our macro file as XLSM file in a specified folder. Using a VBA code, we will filter our dataset to display the sales history of the Fruits only and then save the file. Follow the steps below to do this task.

Steps:

  • Firstly, go to the Developer tab and click on Visual Basic to open the Microsoft Visual Basic editor.

Filtering data Based on a cell value to save excel macro file as filename from cell

  • Secondly, click on the Insert tab and select Module as shown below.

Filtering data Based on a cell value to save excel macro file as filename from cell

  • After that, copy and paste the following VBA code into your module as shown below.
Sub save_cell_value()
Sheets("Filter").Range("B4").AutoFilter Field:=3, Criteria1:=Cells(13, 3).Value
On Error Resume Next
ActiveWorkbook.SaveAs filename:="C:\ExcelDemy\" + Cells(13, 3).Value
End Sub

Filtering data Based on a cell value to save excel macro file as filename from cell

  • Furthermore, click on the Run option or press F5 to run the code.

Filtering data Based on a cell value to save excel macro file as filename from cell

  • As a result, it will filter your dataset and only display the Fruit item’s sales history.

Filtering data Based on a cell value to save excel macro file as filename from cell

  • And finally, if you go to C drive and open the ExcelDemy folder, you will see an XLSM file named after the cell value (Fruit) of your dataset as shown below.

Read More: How to Save a Copy as XLSX Using Excel VBA


2.2 Save as PDF File

The second one is to filter our dataset and save our macro file as PDF file in a specified folder. Using a VBA code, we will again filter our dataset to display the sales history of the Fruits only and then save the file. Follow the steps below to do this job.

Steps:

  • To begin with, go to the Developer tab and click on Visual Basic to open the Microsoft Visual Basic editor.

Using Excel Macro to save excel macro file as filename from cell

  • Then, click on the Insert tab and select Module as shown below.

Using Excel Macro to save excel macro file as filename from cell

  • Next, copy and paste the following VBA code into your module as shown below.
Option Explicit
Sub FilterBasedOnCellValueAnotherSheet()
Dim category As Range
Dim filename As String
With Worksheets("Filter")
Set category = .Range("C14")
End With
With Worksheets("Filter")
With .Range("B4:G11")
.AutoFilter Field:=3, Criteria1:=category, VisibleDropDown:=True
End With
End With
filename = "C:\ExcelDemy\" & Range("C13").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
filename, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Using Excel Macro to save excel macro file as filename from cell

  • Now, click on the Run option or press F5 to run the code.

Using Excel Macro to save excel macro file as filename from cell

  • As a consequence, it will filter your dataset and only display the Fruit item’s sales history.

Using Excel Macro to save excel macro file as filename from cell

  • Hence, if you go to C drive and open the ExcelDemy folder, you will see a PDF file named after the cell value (SaleDetails) of your dataset as shown below.

Read More: Excel VBA to Save Workbook in Specific Folder with Date


Things to Remember

  • Use of VBA Code method will directly convert the file into an XLSX file and save it in the specified folder. The VBA code will not be available in the XLSX file.
  • Filtering data and saving as XLSM file method will also convert the file into an XLSM file and the VBA code will be available in the destination folder file only.
  • Filtering data and saving as PDF file method will convert the file into a PDF file and the VBA code will be available both in your workbook and in the destination folder file.

Download Practice Workbook

You can download the Excel workbook from here.


Conclusion

Hence, follow the above-described steps. Thus, you can easily learn how to save Excel macro files as filename from cell value. Hope this will be helpful. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Asaduzzaman
Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo