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

 

[Fluff]

How about

Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
 
 
  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 Sheet2
          .Cells(nr, 1).Value = sName
          .Cells(nr, 2).Value = r.Address(0, 0)
          .Cells(nr, 3).Value = r.Text
          .Cells(nr, 4).Value = Replace(r.Formula, "=", "")
        End With
      Next r
    End If
  Next i
  Sheet2.Range("A1:D1").Value = Array("Sheet", "Cell", "Error", "Formula")
End Sub

 

[JoeMo]

If the sheet with code name Sheet2 is where you want to list all errors from other sheets:

Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
  
  nr = 1
  For i = 1 To Sheets.Count
    If Sheets(i).CodeName <> "Sheet2" Then
        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 Sheet2
              .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
    End If
  Next i
  Sheet2.Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub

 

[Fluff]

Oops did it for sheet1 & not sheet2.
I've corrected my code in post#2

 

[Webbers]

Sub List_Errors() Dim rErrors As Range, r As Range Dim i As Long, nr As Long Dim sName As String 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 Sheet2 .Cells(nr, 1).Value = sName .Cells(nr, 2).Value = r.Address(0, 0) .Cells(nr, 3).Value = r.Text .Cells(nr, 4).Value = Replace(r.Formula, "=", "") End With Next r End If Next i Sheet2.Range("A1:D1").Value = Array("Sheet", "Cell", "Error", "Formula") End Sub

Hello @Fluff , thanks for your quick reply. I tried your code, but received this error. And when I selected Debug, it referenced this part of the code.

 

[Fluff]

Is the code in the same workbook as the Errors sheet?

 

[Webbers]

If the sheet with code name Sheet2 is where you want to list all errors from other sheets:

Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
 
  nr = 1
  For i = 1 To Sheets.Count
    If Sheets(i).CodeName <> "Sheet2" Then
        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 Sheet2
              .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
    End If
  Next i
  Sheet2.Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub

@JoeMo,

Thanks for the quick response! Your code also came back with the same error as I got with Fluff's. I did add/edit 2 lines of your code as I wanted the formulas to display in column D. That information was on Fluff's code, so I just copied it over to yours. But both broke in the same location, I do not understand. My workbook has 8 tabs, including "Errors", and all tabs (except) Errors should be reviewed for any potential errors.

 

[Webbers]

Is the code in the same workbook as the Errors sheet?

@Fluff,

I initially had it in my Personal;. workbook... as that is where the original code was, which I had made a copy of. I often run code directly from my Personal workbook, so why did it not work in this case?

 

[Fluff]

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.

 

[Webbers]

If the sheet with code name Sheet2 is where you want to list all errors from other sheets:

Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
 
  nr = 1
  For i = 1 To Sheets.Count
    If Sheets(i).CodeName <> "Sheet2" Then
        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 Sheet2
              .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
    End If
  Next i
  Sheet2.Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub

@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!