vba error checking if sheet not found

Question

I've made button on the quick access toolbar that runs a macro to remove chopped up conditional formatting and put clean formatting back in.

The problem is I want this to work in 2 workbooks that do not have the same sheet names. The "Report" sheet is in one workbook and "Data Input" and "XML" sheets are in the other workbook. I need some error checking, so that if the macro does not find a sheet, move on to the next sheet. If sheet not found, move on to the next sheet.

First I remove existing conditional formatting, then go to the first sheet "Report". If it does not find "Report", jump down and try to find "Data Input" sheet.

On Error GoTo Err1

Sheets("Report").Select

CF code

.

Err1

Resume

On Error GoTo Err2

Sheets("Data Input").Select

CF code

.

Err2

Resume

On Error GoTo Err3

Sheets("XML").Select

CF code

.

Err3

Resume

End Sub

Thanks once again for your help.

Answer 1

Hi Wood13,

Replace the second line of the function 

Function WorksheetExists(sName As String) As Boolean

    SheetExists = Application.Evaluate("ISREF('" & sName & "'!A1)")

End Function

with:

      WorksheetExists = Application.Evaluate("ISREF('" & sName & "'!A1)")

===

Regards,

Norman

Answer 2

You could also use a simple function instead of error handling:

If WorksheetExists("Report") Then

    Sheets("Report").Select

    CF code

End If

If WorksheetExists("Data Input") Then

    Sheets("Data Input").Select

    CF code

End If

If WorksheetExists("XML") Then

    Sheets("XML").Select

    CF code

End If

End Sub

Function WorksheetExists(sName As String) As Boolean

    SheetExists = Application.Evaluate("ISREF('" & sName & "'!A1)")

End Function

Answer 3

Wood13 wrote:

On Error GoTo Err1

Sheets("Report").Select

CF code

.

Err1

Resume

On Error GoTo Err2

Sheets("Data Input").Select

CF code

.

Err2

Resume

On Error GoTo Err3

Sheets("XML").Select

CF code

.

Err3

Resume

End Sub

If "CF code" is the same in all cases and only the worksheet name differs, you might consider:

On Error Resume Next

Sheets("Report").Select

If Err <> 0 Then Err.Clear: Sheets("Data Input").Select

If Err <> 0 Then Err.Clear: Sheets("XML").Select

If Err = 0 Then

    CF code

End If

On Error GoTo 0    ' n o t  needed before exiting sub

End Sub

Alternatively, to do exactly what you have above:

On Error Resume Next

Sheets("Report").Select

If Err = 0 Then

    CF code

Else

    Err.Clear

    Sheets("Data Input").Select

    If Err = 0 Then

        CF code

    Else

        Err.Clear

        Sheets("XML").Select

        If Err = 0 Then

            CF code

        End if

    End If

End If

On Error GoTo 0    ' n o t  needed before exiting sub

End Sub

And if you might have more than one of those worksheets in the same workbook in the future:

On Error Resume Next

Sheets("Report").Select

If Err = 0 Then

    CF code

End If

Err.Clear

Sheets("Data Input").Select

If Err = 0 Then

    CF code

End If

Err.Clear

Sheets("XML").Select

If Err = 0 Then

    CF code

End If

On Error GoTo 0    ' n o t  needed before exiting sub

End Sub

Answer 4

except for selecting the other workbook, the code you have is just about correct.

to make a label you do

err1:

so it is terminated with a colon.

On Error GoTo Err1

Sheets("Report").Select

CF code

.

Err1:

Resume Next

On Error GoTo Err2

Workbooks("Otherbook.xlsx").Activate

Sheets("Data Input").Select

CF code

.

Err2:

Resume Next

On Error GoTo Err3

Sheets("XML").Select

CF code

.

Err3:

Resume Next

End Sub

You could also do something like this (if the same commands are executed on each sheet)

Sub abc()

Dim v(1 To 3) As Worksheet

On Error Resume Next

  Set v(1) = Workbooks("Book1.xls").Worksheets("Report")

  Set v(2) = Workbooks("Book2.xls").Worksheets("Data Input")

  Set v(3) = Workbooks("Book2.xls").Worksheets("XML")

On Error GoTo 0

For i = 1 To 3

 If Not v(i) Is Nothing Then

   'CF

    MsgBox v(i).Range("A1").Address(0, 0, xlA1, True)

 End If

Next

End Sub

--

Regards,

Tom Ogilvy