Copying files from one folder to multiple folders based on a list

[praveentheone]

Hi,
I would like to bring to your notice i am receiving an error msg as files missing even though the files exists at the source. I am trying to move certain no of files (say 100) from a source folder to multiple folders and its sub folders. Is there any other methods that works?

Sample workbook and error msg for a similar try attached for reference.
Col. A is the file names for copying
Col. B is the folder names for transfer
Col. C source path
Col. D destination path

Here is the code i use.-

Sub File_Copy()
Dim r As Long
Dim SourcePath As String
Dim dstPath As String
Dim myFile As String
On Error GoTo ErrHandler
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
SourcePath = Range("C" & r)
dstPath = Range("D" & r)
myFile = Range("A" & r)
FileCopy SourcePath & "\" & myFile, dstPath & "\" & myFile
If Range("A" & r) = "" Then
Exit For
End If
Next r
MsgBox "The file(s) can found in: " & vbNewLine & dstPath, , "COPY COMPLETED"
ErrHandler:
MsgBox "Copy error: " & SourcePath & "\" & myFile & vbNewLine & vbNewLine & _
"File could not be found in the source folder", , "MISSING FILE(S)"
Range("A" & r).copy Range("F" & r)
Resume Next
End Sub

 

[Chihiro]

Did you check the destination? I'd assume if file does exist, you'll find all files copied to destination.

The issue is with your "ErrHandler:", you have no logic there to check for error. Therefore, it executes every time.

Nest it inside If Err.Number <> 0 Then ... End If to execute this portion only when there actually is an error.

Ex:

Sub File_Copy()
Dim r As Long
Dim SourcePath As String
Dim dstPath As String
Dim myFile As String
On Error GoTo ErrHandler
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
SourcePath = Range("C" & r)
dstPath = Range("D" & r)
myFile = Range("A" & r)
FileCopy SourcePath & "\" & myFile, dstPath & "\" & myFile
If Range("A" & r) = "" Then
Exit For
End If
Next r
MsgBox "The file(s) can be found in: " & vbNewLine & dstPath, , "COPY COMPLETED"
ErrHandler:
If Err.Number <> 0 Then
    MsgBox "Copy error: " & SourcePath & "\" & myFile & vbNewLine & vbNewLine & _
    "File could not be found in the source folder", , "MISSING FILE(S)"
    Range("A" & r).Copy Range("F" & r)
    Resume Next
End If

End Sub