Welcome back to another #macromondays post. Today I want to share with you how to write a small piece of VBA code which creates a macro to save a copy of your Excel file on daily basis. Each of the daily Excel files will have the current days date added to the filename so they are easily identified. This Excel macro is really useful if you need to have an archiving system of Excel files.

What Does The Macro Do?

This Macro will track the history of your Excel workbook. Each day when you save your file, it will be date and times stamped. Always have a full history of your Excel workbook to hand. You will be able to easily sort your Excel workbooks and find the copy that you need easily. We assign this macro to a button to easily save the workbook.

How Does The Macro Work?

First, we need to create a new module to save this Macro into. Insert a module by opening Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. You can now hit Insert Module.

I have chosen to store this in my Personal Macro Workbook so I can use with any workbook I want to.

Saving it in this location gives me access to the macro so I can choose to run it for any workbook where I want to export and save each worksheet as a new workbook. If you want to read more about the Personal Macro workbook you can check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

If you want to save the macro in the workbook you are working on the insert the module into the current workbook.

Step 1. Set MyDateStamp formatting. I have chosen the following YYMMDD in this way I can easily sort the archived files and find the date of the file I need easily.

Step 2. Here is where we construct the file path. We are using the current workbooks file path. We add in the MyDateStamp value as well as the extension of the workbook. Don’t forget to add this in. In this case, I am using Excel 2016 and it has a file extension of .xlsx.

Step 3. We now used the SaveCopyAs method to save a copy of the Excel workbook and add the file path we have specified in the previous step.

Step 4. Add the Macro to a button in your Excel workbook.

• Insert Tab | Select the shape of the button. I am using a standard rectangle.
• Right-click in the rectangle and select Insert Macro.
• I need to now Select the name of my Macro I want to attach to the button. Mine is called SaveMyFileDaily

Step 5. Test Our VBA Code!

That’s all there is to this small piece of VBA code.

What Next?. Want To Learn Even More About Macros?

So that is how to make the most of the code that you copy from the internet. Why don’t you go grab some code for a repetitive task that you carry out on a regular basis like formatting cells or deleting specific columns? I have some really useful Excel Macros for you to try. You can check out my full list of Macro Mondays blog posts, and the code is always free for you to copy at the end of the blog post.

Next Steps?.

If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.