Are you regularly transferring files from one folder to another? Things can quickly become tedious and time-consuming – but not anymore! Excel VBA Macros enable an easy yet efficient way of copying folders without any hassle. In this guide, we’ll show you the steps needed in order to create your own macro that will move file along with all its content into a destination directory. So get ready for some hands-on experience: Stay tuned as we take you through our simple step by step instructions on how to make your next file transfer faster than ever before!
Right, let’s get started on writing the simple VBA code.
Preparing To Write The 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. You then have a choice,. Either create a module to store your code either in your Personal Macro Workbook or store the code 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. Seeing as I want to reuse this over and over again, and only want excel to be open to rin the macro (not the workbooks I have to copy), then it makes sense to stores the code in my Personal Macro Workbook.
If you want to read more about your PMW then feel free to read my read my other Excel articles 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?
Step 1. Starting The Macro.
First, insert a new module to store the code. In the VBA Editor hit Insert Module in your PMW as in the screen shot below.
Once the module is inserted, then it is time to name the macro. Type Sub the the name of the macro. In this example I name the macro Copy_My_File. Notice once you name the macro Excel inserts the End Sub line of code into the editor. All other lines of code are stores between these two lines.
Here is a screen grab of the the full VBA macro. you can copy the full set of code at the end of this article to use. Always back up your work before running any code.
Step 2. Declare Variables.
The first piece of code is a DIM statement. Two variables are declared. The declaration of variables simply creates a memory container for these values in Excel. I am declaring the Source folder and the Destination folder as below.
Dim SoureFile As String
Dim DestFile As String
Step 3. Set The Source File.
The next step in the macro is to set the source file. This is the full file path and extension to file you want to copy. Update this line of code to reuse in your scenario with the full file path of the file you want to copy from one folder to another.
SourceFile = “C:\Users\EXCEL\Documents\Source\MyFile.xlsx
Step 4. Set The Destination File.
Next, set the destination for the copied file. This again is the full file path of the folder you want to copy the file to. Also update this line of code for your particular scenario.
DestFile = “C:\Users\EXCEL\Documents\Destination\MyFile.xlsx
Step 5. VBA Copy Files From One Folder To Another.
Step 5 is the line of code that copies the file from one folder to another. The variables of the source and destination file are supplied already. The FileCopy VBA statement has two arguments. The Source and Destination. This is the statement that name copies the file from one location to another location.
Note If you try to use the FileCopy statement on a file that is currently open, an error occurs. This is a really useful statement as I do not need to open the source or destination file open to carry out this process.
Step 6. Ending The Macro.
The final step in the code is tp end the macro with the End Sub line of code once the file has been copied. The final line of code is already in the editor. Now, it is time to test the macro. My favourite part. Let’s go!.
So, this article covered how to use vba copy files from one folder to another. I do hope you found this useful. The code is below to copy and use for your own Excel projects. Simply replace the Source and Desintation variables withyour own filepaths. Always backup your work before running any VBA code.
‘Macro by how to excel at excel.com
Sub Copy_My_File
Dim SoureFile As String
Dim DestFile As String
SourceFile = “C:\Users\EXCEL\Documents\Source\MyFile.xlsx
DestFile = “C:\Users\EXCEL\Documents\Destination\MyFile.xlsx
FileCopy SourceFile, DestFile
End Sub
