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]

I haven't tested it, but try something like this...change sheet5 references to whatever sheet number you want the errors logged on:

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

'clear errors
Sheet5.UsedRange.Delete
Set lasterror = 1

'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

Sheet5.Range("A" & lasterror).Value = "Copy error: " & src & "\" & fl
lasterror = lasterror + 1

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

 

[rlv01]

Here is an example of how to use an error handler to trap runtime errors and list them on a separate worksheet.

'Example for logging runtime errors to separate worksheet
Sub ErrorLogDemo()
    Dim WB As Workbook
    Dim ErrorWS As Worksheet
    Dim ErrCnt As Long
    Dim CellRange As Range
    
    Set WB = ActiveWorkbook
    Application.ScreenUpdating = False
    
    'Delete any previous ErrorLog sheet
    On Error Resume Next
    Application.DisplayAlerts = False
    WB.Worksheets("ErrorLog").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    DoEvents

    'Add ErrorLog sheet
    Set ErrorWS = WB.Worksheets.Add(After:=WB.Worksheets(Worksheets.Count))
    ErrorWS.Name = "ErrorLog"
    
    ErrorWS.Cells(1, 1).Value = "Error No."
    ErrorWS.Cells(1, 2).Value = "Error Desc."

    On Error GoTo ErrorLog

    'Create some test errors
    Debug.Print 100 / 0 'div by zero error
    Set CellRange = Range(100)
    WB = 12
    
    'Format result
    With ErrorWS
        .Range("A1:B1").Font.Bold = True
        .Range("A1:B1").Borders(xlEdgeBottom).Weight = xlMedium
        Range("A2").Select
        ActiveWindow.FreezePanes = True
        .Columns.AutoFit
    End With
    Application.ScreenUpdating = True
    Exit Sub

ErrorLog:
    ErrCnt = ErrCnt + 1
    ErrorWS.Cells(ErrCnt + 1, 1).Value = Err.Number
    ErrorWS.Cells(ErrCnt + 1, 2).Value = Err.Description
    Resume Next
End Sub

 

[SBest]

I haven't tested it, but try something like this...change sheet5 references to whatever sheet number you want the errors logged on:

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

'clear errors
Sheet5.UsedRange.Delete
Set lasterror = 1

'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

Sheet5.Range("A" & lasterror).Value = "Copy error: " & src & "\" & fl
lasterror = lasterror + 1

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

Thanks - I am getting an 'object required' at @SeT lasterror = 1'. I have tried tweaking it but can't get it to run

 

[Candyman8019]

Thanks - I am getting an 'object required' at @SeT lasterror = 1'. I have tried tweaking it but can't get it to run

Try changing that line to just : lasterror = 1

 

[SBest]

Try changing that line to just : lasterror = 1

I have tried that, but then getting Object required on Sheet5.UsedRange.Delete

 

[Candyman8019]

Is Sheet5 the actual sheet you're using? You need to "change sheet5 references to whatever sheet number you want the errors logged on"

 

[SBest]

Is Sheet5 the actual sheet you're using? You need to "change sheet5 references to whatever sheet number you want the errors logged on"

Yes, I kept it as Sheet5 for ease

 

[Candyman8019]

Then you need to make sure you have a sheet5 in your workbook. For example, in my sample workbook, I would need to reference Sheet2.

 

[SBest]

Then you need to make sure you have a sheet5 in your workbook. For example, in my sample workbook, I would need to reference Sheet2.
View attachment 100158

Ah, I see what you mean. OK, it is now running without error, but I am getting nothing in the error sheet, even though I have some known non-valid file paths in my data