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……………….