In this short tutorial we will learn how you can use Visual Basic for Applications (VBA) to automate the saving of your Excel 365 spreadsheet. Our code will assign a custom file name, consisting of a prefix concatenated with the current date and time.
Macro to save workbook with VBA
Step #1: Prepare your Excel workbook
First off, create the Excel spreadsheet you would like to save. Ensure that the Developer tab is available in your Excel App, as you will use it in the next step in order to insert your VBA module and code.
Step #2: Add a new VBA module
Next we’ll add a VBA module by following these steps:
- In your Excel workbook, hit the Developer button in the Ribbon.
- Then hit the Visual Basic button (alternatively hit ALT + F11).
- In the Project Explorer add a new Module (or use an existing Module).
Step #3: Save the spreadsheet with VBA
- Add the following code into the VBA Module you have identified:
Sub Save_Spreadsheet_With_Name_Date() Dim CurrentDate As String Dim FilePath As String Dim FileName As String CurrentDate = Format(Date, "yyyy_mm_dd") ' change the file directory and file name as needed. FilePath = "C:\MyDocs\_" FileName = "MyWorkbook_" ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsx" MsgBox("File create succesfully") End Sub
- Save your code.
Step #4: Check your work
- Run you code.
- Using the Windows File Explorer, navigate to the file directory in which you wrote your file.
- Last, go ahead and check the file name to see if it fits your needs.