How to Use Macro to Save Excel File with New Name (5 Ways)

This tutorial will demonstrate how to use a macro to save an excel file with a new name. If you have a particular excel file that you need to use repeatedly, then you should use a macro file. Mostly, when you have used VBA code in your excel file, you need to save the method writing in a macro file. It will help you to protect your work, and make changes easily and you will be able to find it quickly. So, saving a macro excel file with a new name is very essential.

Download Practice Workbook

You can download the practice workbook from here.

5 Suitable Ways to Use Macro to Save Excel File with New Name

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have Salesperson in Column B and Sales in Column C. We will use this dataset to describe all the methods. If you follow the steps correctly, you should learn how to save a macro excel file with a new name on your own. The methods are:

1. Keeping File in Current Location

In this case, our goal is to save an excel file with new name in the current location we are working on using macro. We can do that by following the below steps.

Steps:

• First, press Ctrl+F11 to open the VBA window.
• Next, go to the Insert option and select the Module option.

• Now, the VBA window will open on the screen with the current excel workbook name.

• After that, insert the following code into the window.

Sub SaveFile_1()
Dim File_Name As String
File_Name = "Exceldemy_1"
ActiveWorkbook.SaveAs Filename:=File_Name
End Sub

• Afterward, use the Run option or press F5 to run the code.

• Then, you will find the changed name like the following image.

• Finally, you will get the desired result in the current location of your file.

Read More: How to Save Excel Macro Files as Filename from Cell Value

2. Saving File in New Location

Next, we want to use a macro to save the excel file in a new location with a new name. To fulfill that goal, we have to follow the below steps.

Steps:

• At first, copy the new file location you want to save the file.

• Second, press Ctrl+F11 to open the VBA window and go to the Insert > Module option, and insert the following code there.

Sub SaveFile_3()
ActiveWorkbook.SaveAs Filename:="F:\softeko\article 30"
End Sub

• Third, use the Run option or press F5 to run the code.
• Last, you will get the desired result in your desired new location.

Read More: Excel VBA: Save Workbook as New File in Same Folder

3. Use of GetSaveAsFilename Function

Now, our goal is to save a macro-enabled excel file using the GetAsFilename Function with a new name. The steps of this method are.

Steps:

• To begin with, press Ctrl+F11 to open the VBA window and go to the Insert > Module options.
• In addition, insert the following code here.

Sub SaveFile_2()
Dim File_Name As Variant
File_Name = Application.GetSaveAsFilename
If File_Name <> False Then
  ActiveWorkbook.SaveAs Filename:=File_Name
End If
End Sub

• Furthermore, in the File name option give the file the desired name and Save it in the desired folder on your pc.

• Finally, you will get the desired result.

Read More: Excel VBA Save as File Format (12 Suitable Examples)

4. Specifying File Type Before Saving

In many cases, you need to use many functions or many types of files while doing a project. So, it is very important to specify the file type so that it is easier to use or check next time for myself or any other person. The steps of this method are.

Steps:

• Firstly, press Ctrl+F11 to open the VBA window and go to the Insert > Module options.
• Secondly, insert the following code here.

Sub SaveFile_4()
Dim File_Path As String
File_Path = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=File_Path & ".xlsm"
End Sub

• Thirdly, use the Run option or press F5 to run the code.
• Fourthly, in the File name option give the file the desired name and Save it in the desired folder on your pc.

• Lastly, you will get the desired result.

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

5. Inserting Filename from Cell

Now, our target is to insert a new file name using macro from the cell to save the excel file. To do so, we have to follow the below steps.

Steps:

• To begin with, insert the desired filename in any blank cell( in this case cell C12).

• In addition, press Ctrl+F11 to open the VBA window and go to the Insert > Module option, and then insert the following code here.

Sub SaveFile_5()
Dim Shell_1 As Object
Dim File_name, Full_path As String
Set Shell_1 = CreateObject("WScript.Shell")
DeskTop_Path = Shell_1.SpecialFolders("Desktop")
File_name = Range("C12").Value
Full_path = DeskTop_Path + "\" + File_name + ".xlsm"
ActiveWorkbook.SaveCopyAs Full_path
MsgBox ("File is saved at " + Full_path)
End Sub

• Furthermore, use the Run option or press F5 to use the code and you will get the below command on your screen.

• Finally, you will get the desired result.

Read More: Excel VBA to Save as File Using Path from Cell (With Quick Steps)

How to Save Worksheet in Separate File in Excel

When doing a  project with lots of data, you need to save a worksheet to a separate file for ease to use. Now, we will try to learn about saving worksheets to separate files in excel. The steps of this process are described below.

Steps:

• At first, right-click on the worksheet you want to separate.

• Second, the Move or Copy dialog box will come on the screen. Here select the new book option then click on Create a copy and press OK.

• Last, you will get the desired result.

Things to Remember

• To run all the methods you have to save the files in the (.xlsm) mode. Otherwise, it won’t work.
• Among all the methods, the first method is the easiest and most efficient to use.
• If you don’t want to use the VBA code option to change the name, then you can save the file manually, and after that rename the file accordingly.

Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to save an excel file with a new name using macro. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Excel VBA: Save Sheet as New Workbook without Opening
• Excel VBA: Save Workbook without a Prompt (with Easy Steps)
• How to Save a Copy as XLSX Using Excel VBA (5 Suitable Ways)
• Excel VBA to Save File with Variable Name (5 Examples)