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

Get FREE Advanced Excel Exercises with Solutions!

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


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

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:

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


1. Keeping File in Current Location

In this case, our goal is to save an Excel file with a 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.

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

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

Suitable Ways to Use Macro to Save Excel File with New 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

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

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

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

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

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

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

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

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:

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

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

  • 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

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

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

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

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 as follows.

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

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

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

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

  • Finally, you will get the desired result.

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

Read More: Excel VBA Save as File Format


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 as follows.

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 a 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


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.

Download Practice Workbook

You can download the practice workbook from here.


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. Don’t forget to drop comments, suggestions, or queries if you have any 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.
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo