Md. Asaduzzaman
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!
Download Practice Workbook
You can download the Excel workbook from here.
2 Suitable Ways to Save Excel Macro Files as Filename from Cell Value
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.
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.
- Now, click on the Insert tab and select Module as shown below.
- 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
- Furthermore, click on the Run option or press F5 to run the code.
- 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: Excel VBA to Save as File Using Path from Cell (With Quick Steps)
Similar Readings
- How to Use Macro to Save Excel File with New Name (5 Ways)
- Excel VBA: Save Workbook as New File in Same Folder
- Excel VBA to Save Workbook in Specific Folder with Date
- How to Get Filename from Path in Excel (6 Simple Methods)
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.
- Secondly, click on the Insert tab and select Module as shown below.
- 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
- Furthermore, click on the Run option or press F5 to run the code.
- As a result, it will filter your dataset and only display the Fruit item’s sales history.
- 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.
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.
- Then, click on the Insert tab and select Module as shown below.
- 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
- Now, click on the Run option or press F5 to run the code.
- As a consequence, it will filter your dataset and only display the Fruit item’s sales history.
- 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: How to Save a Copy as XLSX Using Excel VBA (5 Suitable Ways)
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.
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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.
