Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my 2020 series. Do you want to quickly create a backup of your Excel workbook in the same folder as your original one?. Have you got a really important Excel solution which you need to have backed up in the same directory as your original one?. Well, here is a super quick Excel macro which will take care of this for you. It will even add the current date that you backed up the file. So let’s
Preparing To Write The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Hit ALT +F11 or
- Select the Developer Tab | Code Group | Visual Basic.
Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW then check out my blog posts below.
- Creating and Updating Your Personal Macro Workbook
- Create A Shortcut To Your Personal Excel Macro Workbook
- Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro.
Let’s get started and write the VBA code and create a backup of your current Excel workbook. First, I want to insert a new module in my PMW. To start the macro type Sub and then the name of your macro. For this example, I have simply named the macro MyBackup. Note that Excel automatically inserts the End Sub code. All we need to do is insert the rest of the VBA code in between these two lines. [stextbox id=’info’] Sub MyBackup()[/stextbox]
Use The Workbook.SaveCopyAs method
We will use the Workbook.SaveCopyAs method for this macro. This will save a copy of the workbook to a file but doesn’t modify the open workbook in memory. The Syntax of this method is :-
Syntax
expression.SaveCopyAs (FileName) expression A variable that represents a Workbook object.
The filename parameter is required and specifies the file name for the copy of the file.
[stextbox id=’info’] ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path _ & “\Copy ” & Format(Now, “yy-mm-dd”) & ” ” & ActiveWorkbook.Name[/stextbox] We use the ActiveWorkbook.SaveCopyAs in this example as the ActiveWorkbook refers to the currently active workbook. This is exactly what we want. The rest of the statement is fairly long. So let’s break it down. [stextbox id=’info’]ActiveWorkbook.Path[/stextbox] this is the file path of the currently active workbook. [stextbox id=’info’]“\Copy " and " “[/stextbox] These are text strings. The first string specifies that the first word in the file name is “Copy". The second string adds a space ( ). [stextbox id=’info’]Format(Now, “yy-mm-dd")[/stextbox] Now returns today’s date and the current time. ( Alternatively, we could have used the Date function). The format command takes the date returned by Now and formats it according to the date format “yy-mm-dd". (You can choose any alternative date format also).
Ending The Macro.
Once the copy of the workbook has been created the macro ends. We already have the End Sub piece of code from when we started the macro. [stextbox id=’info’] End Sub[/stextbox]
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series then click on the link below
Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money-back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free. 