How to copy and paste file using Excel VBA ?

This code can help you to achieve different tasks to copy and paste files using the automated process. Below are few examples where this code can be used:

1. Copy and paste files from one location to other as a backup
2. Copy and paste processed files from local desktop or drive to Customer location or FTP location
3. Schedule a copy paste macro, for trigger as per specific requirement of job
4. Copy paste file, in order to process the copied file and keep original as backup

…and you can write as many, as per your job requirements.

You can directly copy paste the below code in your VBA module and use it by changing few variables. Check out the comments in Green, wherever you would need to tweak the code to get the desired output.

—Code start

Sub Copy_File()

‘ Declare variables we are going to use. All variables are declared as string, as we are going to assign text values to them
Dim filename As String
Dim Source As String
Dim Destination As String

‘Dim means dimension, which we are using to declare the variables

‘Next step will be to set values to our declared variables
filename = “Desert_backup”
Source = “C:\Test\Desert.jpg”
Destination = “C:\Test\Backup\” & filename & “.jpg”

‘We have now set values or assigned values to our variables. In the example above, we are trying to copy a file named Desert.jpg, which is saved in Test folder in my C drive to folder named Backup under Test folder in my C drive. Refer the above path in the code i.e. Source and Destination.

‘Also while doing so, we are renaming the back up file from Desert.jpg to Desert_backup.jpg.

‘Below is the final code, which will copy the file from source to destination, with new name
FileCopy Source, Destination

‘ We have inbuilt FileCopy VBA function to complete the copy paste process. The FileCopy function’s standard script is FileCopy(Source, Destination) which means, this function copies file from one folder or directory to other

‘ Remember if the file you are trying to copy is open, then the macro will give a run-time error. i.e. in order to copy the file, the file needs to be closed.

End Sub

—Code end

Just in case if you are planning to use the code to take the server back up daily or weekly once, so instead of renaming file to _backup, you can just add a timestamp at the end of file name, in order to save the file with the specific date and time stamp of the back up day.

Check below code in which we are using date time stamp to rename and save the file. The only different from above code is the tweak done in the Destination string.

—Code start

Sub Copy_File_dt ()

‘ Declare variables
Dim filename As String
Dim Source As String
Dim Destination As String

‘Set values to variables
filename = “Desert_”
Source = “C:\Test\Desert.jpg”
Destination = “C:\Test\Backup\” & filename & Format(Now(), “mm_dd_yyyy hh mm AMPM”) & “.jpg”

‘Here we have used 2 inbuilt VBA functions Now() and Format. Now() function will get you the current value of date and time. While the Format function helps you to format the date time, in whatever format we need as output

‘Final code for file copy
FileCopy Source, Destination

End Sub

—Code end

In order to run the above code for multiple files copy paste, you will need to tweak the above code, to assign dynamic values to variables and add a Loop code to run till all the files are copied.

You can also get a list of files that you want to copy and also track how many files actually got copied, in order to verify or validate the output. But right now i want to keep this post simple, just to help you understand the simple code, on how to copy paste the file.

Thank you for reading. Happy Learning !