Is there a certain workheet in a workbook?

[kapfrank]

Is it possible to check if there is a specific worksheet in a workbook.
And when this worksheet is available to delete it, if not go on with the next step.

For example:
I’ve a workbook called BOOK_1, it has the worksheet TEMP. So I have to delete the worksheet TEMP.
But’s also possible that there isn’t a worksheet called TEMP, in workbook BOOK_1, then the macro has to go on with the next step.

I think that this should be done with an:
If….Then…..Else
statement.

 

[mikerickson]

On Error Resume Next
If Workbooks("Book_1").Sheets("Temp").Name="Temp" Then
    Msgbox "it's there"
Else
    msgBox "No such sheet"
End If
On Error Goto 0

 

[kapfrank]

Mikerickson,

Thank you for the code.

But this is working when there is only 1 worksheet in the workbook.

I've a workbook with 100 worksheets in it!.
But with your example i rewrote it, and came to the next example code

Sub Family()
    If Workbooks("Family").Sheets("Frank").Name = "Erik" Then
        MsgBox "it's there"
    Else
        If Workbooks("Family").Sheets("Miranda").Name = "Erik" Then
            MsgBox "it's there"
        Else
            If Workbooks("Family").Sheets("Noa").Name = "Erik" Then
                MsgBox "it's there"
            Else
                MsgBox "No such sheet"
            End If
        End If
    End If
End Sub

It's checking if there is a worksheet called "Erik".
But i have to put in all the names of the different worksheets.

Is there an easier way to check this?
Because in my cause I have to write 100 times the same code “if….then….else”.

 

[mikerickson]

This is all the code needed to determine if the book "Family.xls" has a sheet named "Erik"

Dim bookName As String, sheetName As String
bookName = "Family.xls"
sheetName = "Erik"
On Error Resume Next
If Workbooks(bookName).Sheets(sheetName).Name = sheetName Then
    MsgBox "It's there"
Else
    MsgBox "No such sheet in " & bookName
End If
On Error GoTo 0

 

[mikerickson]

I was mistaken. The code I posted works on my Mac but not a PC. (Different error handling, I guess)
This has been tested on a PC and should also work on a Mac.

Dim bookName As String, sheetName As String
bookName = "Family.xls"
sheetName = "Erik"
On Error Resume Next
Workbooks(bookName).Sheets(sheetName).Name = sheetName
If Err Then
    MsgBox "No such sheet in " & bookName
Else
    MsgBox "It's there"
End If
On Error GoTo 0

 

[NateO]

Hello,

A pretty common way of testing whether an Object actually exists, amongst varying platforms, is to attempt to assign it to an appropriate Object Variable and then test whether that Variable has been successfully initialized. E.g.,

Sub foo()
Dim ws As Worksheet, tmpBool As Boolean
Const WSName As String = "Foobar"
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(WSName)
On Error GoTo 0
Let tmpBool = Not ws Is Nothing
MsgBox "Worksheet Existence Factor? " & tmpBool
If tmpBool Then Set ws = Nothing
End Sub

 

[kapfrank]

Thank you.
The last code worked.

 

[NateO]

You are welcome.