VBA Error Logging to tab on same Spreadsheet

[SBest]

I have a spreadsheet that copies a load of files, based on a file path in Column A and pastes them based on a file path in Column B.

In some cases, some of the file paths specified in Column A won't exist. At the moment these errors just come up as a message box. I want to add something to my vba so that these errors are outputted to a tab 'Errors' in the same workbook, with this list being cleared every time that the macro is run. I am struggling though!

This is the original vba

Sub CopyFiles()
Dim src As String, dst As String, fl As String
Dim lr As Long
'Source directory
'Range("A2").Select
lr = Cells(Rows.Count, "C").End(xlUp).Row
For X = 2 To lr
src = Range("A" & X).Value
'Destination directory
dst = Range("B" & X).Value
'Filename
fl = Range("F" & X).Value
On Error Resume Next
'get project id
FileCopy src & "\" & fl, dst & "\" & fl
If Err.Number <> 0 Then
MsgBox "Copy error: " & src & "\" & fl
End If
Next X
On Error GoTo 0
MsgBox ("Complete!")
End Sub

 

[Candyman8019]

on your source sheet, do you have data in column C? On the line where you're finding the last row, try using column A rather than C

 

[SBest]

on your source sheet, do you have data in column C? On the line where you're finding the last row, try using column A rather than C

Yes, Column C is where the user enters data and Cols A & B populate based on what is entered into Column C.

I have tried using A rather than C for last row, but still nothing in the errorlog.

Appreciate all your help with this!

 

[Candyman8019]

This code works for me, can you compare it to what you have:

Sub CopyFiles()
Dim src As String, dst As String, fl As String
Dim lr As Long
Dim lasterror As Integer

'clear errors
Sheet2.UsedRange.Delete
lasterror = 1

'Source directory
lr = Cells(Rows.Count, "C").End(xlUp).Row

For x = 2 To lr
src = Range("A" & x).Value
'Destination directory
dst = Range("B" & x).Value
'Filename
fl = Range("F" & x).Value
On Error Resume Next
'get project id
FileCopy src & "\" & fl, dst & "\" & fl
If Err.Number <> 0 Then
Sheet2.Range("A" & lasterror).Value = "Copy error: " & src & "\" & fl
lasterror = lasterror + 1

End If
Next x
On Error GoTo 0
MsgBox ("Complete!")
End Sub

Additionally, step through your code using F8 and make sure it does what it is supposed to with each line.

 

[SBest]

This code works for me, can you compare it to what you have:

Sub CopyFiles()
Dim src As String, dst As String, fl As String
Dim lr As Long
Dim lasterror As Integer

'clear errors
Sheet2.UsedRange.Delete
lasterror = 1

'Source directory
lr = Cells(Rows.Count, "C").End(xlUp).Row

For x = 2 To lr
src = Range("A" & x).Value
'Destination directory
dst = Range("B" & x).Value
'Filename
fl = Range("F" & x).Value
On Error Resume Next
'get project id
FileCopy src & "\" & fl, dst & "\" & fl
If Err.Number <> 0 Then
Sheet2.Range("A" & lasterror).Value = "Copy error: " & src & "\" & fl
lasterror = lasterror + 1

End If
Next x
On Error GoTo 0
MsgBox ("Complete!")
End Sub

Additionally, step through your code using F8 and make sure it does what it is supposed to with each line.

Thank you Joe - that works. I wasn't referencing my sheet name properly in this bit "Sheet2.Range("A" & lasterror).Value = "Copy error: " & src & "\" & fl"

Much appreciated!