Excel VBA code to Copy File from one location to another location

Click here to download the workbook which contains the below code

Copy below code and paste it in the VBA Code Window . Then update the File and folder names in the Excel file. Now click F5 to execute the code.

Sub Copy_files()
'www.comexcelhub.com
'This Code will copy the file form one folder to another Folder
'Source Folder name, Destination Folder Name and Source File Names should be
entered in the Excel WOrkbook Sheet1

Dim FSO As Object
Dim sour_File As String
Dim Src_Folder As String
Dim Destin_Folder As String
Dim Ttl_Fls, F_cntr As Long

Ttl_Fls = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For F_cntr = 2 To Ttl_Fls

Range("D" & F_cntr) = "=CONCATENATE(A" & F_cntr & ",""\"")"
Range("E" & F_cntr) = "=CONCATENATE(B" & F_cntr & ",""\"")"

'This is Your File Name which you want to Copy stored in the variable
sour_File = Range("C" & F_cntr)

'This is Your Source file FOlder path from Where you want to Copy the File
Src_Folder = Range("D" & F_cntr)

'This is Your Destination file FOlder path from Where you want 
to Paste the File
Destin_Folder = Range("E" & F_cntr)

'Create Object
Set FSO = CreateObject("Scripting.FileSystemObject")

'Below Code will Checking If File Is Located in the Source Folder
If Not FSO.FileExists(Src_Folder & sour_File) Then
    MsgBox "Specified File Not Found", vbInformation, "Not Found"
    
'Copying If the Same File if it is Not Located in the Destination Folder
ElseIf Not FSO.FileExists(Destin_Folder & sour_File) Then
    FSO.CopyFile (Src_Folder & sour_File), Destin_Folder, True
    MsgBox "Specified File Copied Successfully", vbInformation, "Done!"

Else
'Below Message is shown if already destination folder contains same File.
    MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
End If

Next F_cntr

End Sub

Explanation of the Code Lines

Below 4 Lines are Variable Declarations.

Dim FSO As Object
Dim sour_File As String
Dim Src_Folder As String
Dim Destin_Folder As String
Dim Ttl_Fls, F_cntr As Long

Line1: Ttl_Fls = ThisWorkbook.Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row

In Line1 we are storing the total number of files we want to copy

Line2: For F_cntr = 2 To Ttl_Fls (In Line2 loop will start between rows to get each file name.)

Line3: Range(“D” & F_cntr) = “=CONCATENATE(A” & F_cntr & “,””\””)”
Line4: Range(“E” & F_cntr) = “=CONCATENATE(B” & F_cntr & “,””\””)”

In line 3 &4 we are updating formula to create the folder path as per macro requirement (it will simply add “\” at the end of the folder name which is pasted by the user in “A” and “B” columns)

Line5: sour_File = Range(“C” & F_cntr) (In line 5 we updating filename to variable)

Line6: Src_Folder = Range(“D” & F_cntr) (In line 6 we updating Source Foldername to variable)

Line7: Destin_Folder = Range(“E” & F_cntr) (In line 7 we updating destination Foldername to variable)

Line8: Set FSO = CreateObject(“Scripting.FileSystemObject”) (In line 8 we are updating object value to get foloder and pickup files in variable)

Line9: If Not FSO.FileExists(Src_Folder & sour_File) Then (In Line 9 we are checking do we have the file which we should copy in source folder)

Line10: MsgBox “Specified File Not Found”, vbInformation, “Not Found” (In Line10 we get the message box if the file is not found in the source folder)

Line11: ElseIf Not FSO.FileExists(Destin_Folder & sour_File) Then (In Line11 we are checking if the destination folder already contains the copied file)

Line12: FSO.CopyFile (Src_Folder & sour_File), Destin_Folder, True (In-Line 11 if we do not have the source file in destination folder then in Line12 we are copying the file to destination folder)

Line13: MsgBox “Specified File Copied Successfully”, vbInformation, “Done!”(Inline 13 we are showing the message that File is copied)

Line14: Else (In Line11 if already we have source file then we have else condition to show the message in Line 15)
Line15: MsgBox “Specified File Already Exists In The Destination Folder”, vbExclamation, “File Already Exists”( In-Line 15 we are showing the message that already we have source file in destination folder)
Line16: End If (In-Line 16 to close the if condition)

Video on the above code will be available here shortly……………….