The attached Fil has a macro that checks if the Sheet "Errors" exists and if so it clears the Sheet.
It then searches and lists the errors on the Errors Sheet
It then Dates Stamps the Sheet
Do I need any error handling for this part of the macro ?
The problem is that if the Sheet doesn't exist then it errors.
Any suggestions
Thank You
Allister
It then searches and lists the errors on the Errors Sheet
It then Dates Stamps the Sheet
Do I need any error handling for this part of the macro ?
The problem is that if the Sheet doesn't exist then it errors.
Any suggestions
Thank You
Allister
VBA Code:
Sub List_Errors()
Dim rErrors As Range, r As Range
Dim i As Long, nr As Long
Dim sName As String
If (Worksheets("Errors").Name <> "") Then
Sheets("Sheet1").Cells.ClearContents
Else
Sheets.Add Before:=Sheets(1)
ActiveSheet.Name = "Errors"
End If
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")
'Add Header
Range("A1").Select
ActiveCell.FormulaR1C1 = "As Of "
Range("B1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("B1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:B1").Select
Selection.Font.Bold = True
End Sub
Errors.xlsm | |||
---|---|---|---|
D | |||
33 | |||
Sheet2 |