Hello Excellers and welcome back to another Excel Macro blog post in my #MacroMonday 2020 series. Today I will walk you through how to write simple Excel VBA macro which will save an Excel file. What is new about that?? Well this code will save the Excel file to multiple locations. Handy right?.
So, if you regularly save the same file to a multiple locations then this is the Excel macro for you. I have a file I need to save to two locations, one for me and one for my team. I usually save my file then copy it across to the alternative location. No need to anymore. Just run the macro.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Before we begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What’s the difference?.
Well, if you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb. This is a very useful macro. It is very specific to this workbook process but i do not want to save the workbook as an macro extension. I therefore still want to save it in my personal macro workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Preparing To Write The Macro.
We need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it SaveMyFile. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines of code.
[stextbox id=’info’]
Sub SaveMyFile()
End Sub [/stextbox]
Declare Variables.
Next, I need to declare just one variable in this macro. Declaring variables simply creates a memory container for these values. I am declaring the variable MyFile As String. I have used the variable type as string as it can consist of text, numerical values, date and time and alphanumeric characters. This is perfect for an Excel file name. I also have set the Filename to refer to the Active Workbook and it’s full name.
[stextbox id=’info’]
Dim MyFile as String
MyFile= ActiveWorkbook.FullName
End Sub [/stextbox]
Setting The Locations To Save The File.
The next part of the code will save the file to two different folders on the same drive. In this case it the desktop. The file is saved with the same filename.
The current location of the file is set, so it can save it to the current location last. Excel will ask if you want the existing version of the workbook to be overwritten. This allows the last save to be in the original location.
[stextbox id=’info’]
ActiveWorkbook.SaveAs “C:\Users\EXCEL\Desktop\Sales_Copy”+
ActiveWorkbook.Name
ActiveWorkbook.SaveAs MyFile
End Sub [/stextbox]
Ending The Macro.
Finally, the code ends once the file save has been completed with the “End Sub” piece of code. This was already entered into the module when the name of the macro was set.
[stextbox id=’info’]
End Sub
[/stextbox]
If you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Blog Posts.
