VBA code - Moving files not working

[superfb]

Sub copy()
Dim r As Long
Dim SourcePath As String
Dim dstPath As String
Dim myFile As String
Set FSO = CreateObject("Scripting.FileSystemObject")

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

i have a list of files in column d that i would like to move as per col e however nothing is copying over......can someone please help?

 

[Domenic]

Yes it gets picked up via the msg box

In that case, comment out or temporarily delete...

On Error GoTo ErrHandler

Then, run the code again. This time, when it errors out, see what values have been assigned to SourcePath and dstPath, and see what type of error it is.

Which error number is it, and what does the description say?

 

[superfb]

Hi,

so i believe the file does get copied over, but whatever the last row of data is.....the VBA code tells the message box to appear to say there is an error with whatever the last file is..........

 

[superfb]

In that case, comment out or temporarily delete...

On Error GoTo ErrHandler

Then, run the code again. This time, when it errors out, see what values have been assigned to SourcePath and dstPath, and see what type of error it is.

Which error number is it, and what does the description say?

Run time error 20 appears

 

[Domenic]

Run time error 20 appears

That's because no error has occurred when the statement "Resume Next" is executed, and so there's nowhere to resume to. You should exit the sub after exiting the "For/Next" loop and displaying the message box that notifies the user where the files can be found. You can use the following line...

Exit Sub

However, instead of using "On Error GoTo ErrHandler/Resume Next" to essentially check whether a file exists, I would suggest you use the FileExists method of the FileSystemObject...

If fso.FileExists(SourcePath) Then

 

[superfb]

That's because no error has occurred when the statement "Resume Next" is executed, and so there's nowhere to resume to. You should exit the sub after exiting the "For/Next" loop and displaying the message box that notifies the user where the files can be found. You can use the following line...

Exit Sub

However, instead of using "On Error GoTo ErrHandler/Resume Next" to essentially check whether a file exists, I would suggest you use the FileExists method of the FileSystemObject...

If fso.FileExists(SourcePath) Then

Thank you for the reply

should the code look like this

Sub copy()
Dim r As Long
    Dim SourcePath As String
    Dim dstPath As String
    Dim myFile As String
    Dim FSO As Object
    
     Set FSO = CreateObject("Scripting.FileSystemObject")
     
   ' On Error GoTo ErrHandler
   If FSO.FileExists(SourcePath) Then
    For r = 2 To Range("D" & Rows.Count).End(xlUp).Row
    SourcePath = Range("D" & r)
    dstPath = Range("E" & r)
    myFile = Range("A" & r)
        FSO.CopyFile SourcePath, dstPath & "\"
        If Range("D" & r) = "" Then
           Exit For
        End If
    Next r
        MsgBox " Number of files copied over: " & r - 2 & vbNewLine, , "COPY COMPLETED"
        Exit Sub
'ErrHandler:
    'MsgBox "Copy error: " & SourcePath & vbNewLine & vbNewLine & _
    '"File could not be found in the source folder", , "MISSING FILE(S)"
'Range("A" & r).copy Range("E" & r)
Resume Next
End Sub

[/QUOTE]


but i recieve the following error block if without end if

 

[Domenic]

Maybe something like this...

Option Explicit

Sub copy()


    Dim r As Long
    Dim FileCount As Long
    Dim SourcePath As String
    Dim dstPath As String
    Dim myFile As String
    Dim FSO As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
     
    On Error GoTo ErrHandler
    
    FileCount = 0
    For r = 2 To Range("D" & Rows.Count).End(xlUp).Row
        SourcePath = Range("D" & r).Value
        dstPath = Range("E" & r).Value
        'myFile = Range("A" & r).Value
        If FSO.FileExists(SourcePath) Then
            FSO.copyfile SourcePath, dstPath & "\"
            FileCount = FileCount + 1
        End If
    Next r
    
    MsgBox "Number of files copied over: " & FileCount, vbInformation, "COPY COMPLETED"
    
ExitHandler:
    Set FSO = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
    Resume ExitHandler
    
End Sub

For a file that does not exist, you could have "N/A" returned in a cell in the corresponding row. So, for example, if you wanted to use Column F, you would replace...

        If FSO.FileExists(SourcePath) Then
            FSO.copyfile SourcePath, dstPath & "\"
            FileCount = FileCount + 1
        End If

with

        If FSO.FileExists(SourcePath) Then
            FSO.copyfile SourcePath, dstPath & "\"
            FileCount = FileCount + 1
        Else
            Range("F" & r).Value = "N/A"
        End If