Determine If Worksheet Exists

[AllisterB]

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

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

 

[Trebor76]

Hi AllisterB,

Try this:

Option Explicit
Sub List_Errors()
  
    Dim rErrors As Range, r As Range
    Dim i As Long, nr As Long
    Dim sName As String
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
  
    On Error Resume Next
        Set ws = ThisWorkbook.Sheets("Errors")
        If Err.Number <> 0 Then
            Sheets.Add(Before:=ThisWorkbook.Sheets(1)).Name = "Errors"
        Else
            ws.Cells.ClearContents
        End If
    On Error GoTo 0
  
'  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
    
    Application.ScreenUpdating = True
  
End Sub

Regards,

Robert

 

[AllisterB]

[Thanks - works great

 

[Trebor76]

Thanks for letting us know and you're welcome

 

[AllisterB]

What say the workbook doesn't have a sheet called "Errors" - what part of eth code handles this ?

 

[Trebor76]

The first block of code i.e. this:

On Error Resume Next
        Set ws = ThisWorkbook.Sheets("Errors")
        'If there's an error setting the 'ws' variable, then...
        If Err.Number <> 0 Then
            '...there's no sheet called 'Errors' in the workbook, so create it as the the first tab
            Sheets.Add(Before:=ThisWorkbook.Sheets(1)).Name = "Errors"
        'Else...
        Else
            '...it does exist so just clear its contents
            ws.Cells.ClearContents
        End If
    On Error GoTo 0

I've added comments to show you what the code is doing.

Thanks,

Robert