VBA Error Logging to tab on same Spreadsheet

SBest

New Member
Joined
Sep 7, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 
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
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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!
 
Upvote 0
This code works for me, can you compare it to what you have:
VBA Code:
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.
 
Upvote 0
Solution
This code works for me, can you compare it to what you have:
VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top