Need Updates to Error List Macro

[Webbers]

Hi all!

I found a code, and it works perfectly, as it has, but I now need to make some modifications, and I am having difficulty doing so. Thank you in advance, your time and help are greatly appreciated!!!!!!

1. I do not want a NEW sheet added to the beginning of the workbook. I have a sheet called "Errors" (which has a code name of Sheet2). I would prefer to use the code name if possible, as users often change the sheet names... which I would have no control of.
2. I would like the formula to display in column D on the Errors tab. This is very helpful in my development process and when I am training others. The visualization of the errors.

Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
  
  Sheets.Add Before:=Sheets(1)
  nr = 1
  For i = 2 To Sheets.Count
    Set rErrors = Nothing
    On Error Resume Next
    Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors)
    On Error GoTo 0
    If Not rErrors Is Nothing Then
      sName = Sheets(i).Name
      For Each r In rErrors
        nr = nr + 1
        With Sheets(1)
          .Cells(nr, 1).Value = sName
          .Cells(nr, 2).Value = r.Address(0, 0)
          .Cells(nr, 3).Value = r.Text
        End With
      Next r
    End If
  Next i
  Sheets(1).Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub

 

[Webbers]

You can only refer directly to a sheet codename if it's in the same workbook as the code.
Your basic options are
1) keep the code in the workbook it needs to be used on.
2) use the sheet name & not the code name
3) loop through the sheets to find the correct codename & assign that to a variable.

Also when using codenames I would advise changing them, rather than using the defaults. Sheet codenames work in the same way as sheet names, in that if you delete Sheet1 & then later on add a new sheet that will become Sheet1.

@Fluff ,
That makes perfect sense. I appreciate you taking the time to explain. This code will be in my Personal workbook too... with the addition of commented notes for reference of what you just told me. I am so grateful for your time, help and the additional explanation!

 

[JoeMo]

@JoeMo ,

At Fluff's suggestion (for his code), I moved the code to the workbook I was working in (rather than personal), and your code worked perfectly. Thanks for your help!

You are welcome - thanks for the reply.