Reputation: 1571

Test or check if sheet exists

Dim wkbkdestination As Workbook
Dim destsheet As Worksheet

For Each ThisWorkSheet In wkbkorigin.Worksheets 
    'this throws subscript out of range if there is not a sheet in the destination 
    'workbook that has the same name as the current sheet in the origin workbook.
    Set destsheet = wkbkdestination.Worksheets(ThisWorkSheet.Name) 
Next

Basically I loop through all sheets in the origin workbook then set destsheet in the destination workbook to the sheet with the same name as the currently iterated one in the origin workbook.

How can I test if that sheet exists? Something like:

If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then

Upvotes: 156

Answers (24)

Tim Williams

Reputation: 166126

Some folk dislike this approach because of an "inappropriate" use of error handling, but I think it's considered acceptable in VBA... An alternative approach is to loop though all the sheets until you find a match.

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet
        
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Worksheets(shtName) 'edited from wb.Sheets
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

Edit: shorter version

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    WorksheetExists = (wb.Worksheets(shtName).Name = shtName)
End Function

Upvotes: 249

Geoff Kendall

Reputation: 1415

No need to loop or use a function, that I can see?

If wkbkdestination.Worksheets(ThisWorkSheet.Name).Name = ThisWorksheet.Name Then

'Do stuff

Else

'Go to end of loop

End If

Upvotes: 0

Peter Albert

Reputation: 17475

As checking for members of a collection is a general problem, here is an abstracted version of @Tim's answer:

Function Contains(objCollection As Object, strName as String) As Boolean
    Dim o as Object
    On Error Resume Next
    set o = objCollection(strName)
    Contains = (Err.Number = 0)
    Err.Clear
 End Function

This function can be used with any collection like object (Shapes, Range, Names, Workbooks, etc.).

To check for the existence of a sheet, use If Contains(Sheets, "SheetName") ...

Upvotes: 23

Excel Hero

Reputation: 14764

Short and clean:

Function IsSheet(n$) As Boolean
    IsSheet = Not IsError(Evaluate("'" & n & "'!a1"))
End Function

Upvotes: 4

Alex Johnson

Reputation: 11

I use this function to check and return a new sheet name if needed. WSname is the desired worksheet name and WBCur is the workbook you would like to check in. I use this because there is no need for error handling and can call it whenever i am creating a new worksheet.

Public Function CheckNewWorksheetName(WSName As String, WBCur As Workbook) 'Will return New Name if needed
    Dim NewWSNum As Long, A As Integer, B As Integer, WorksheetFound As Boolean
    NewWSNum = 1
    WorksheetFound = False
    For A = 1 To WBCur.Worksheets.Count
        If WBCur.Worksheets(A).Name = WSName Then
            A = WBCur.Worksheets.Count
            WorksheetFound = True
        End If
    Next A
    
    If WorksheetFound = False Then
        CheckNewWorksheetName = WSName
    Else
        Do While WorksheetFound = True
            WorksheetFound = False
            For B = 1 To WBCur.Worksheets.Count
                If WBCur.Worksheets(B).Name = WSName & "_" & NewWSNum Then
                    B = WBCur.Worksheets.Count
                    WorksheetFound = True
                    NewWSNum = NewWSNum + 1
                End If
            Next B
        Loop
        CheckNewWorksheetName = WSName & "_" & NewWSNum
    End If
End Function

Upvotes: 0

fbonetti

Reputation: 6672

You don't need error handling in order to accomplish this. All you have to do is iterate over all of the Worksheets and check if the specified name exists:

Dim exists As Boolean

For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "MySheet" Then
        exists = True
    End If
Next i

If Not exists Then
    Worksheets.Add.Name = "MySheet"
End If

Upvotes: 73

Guest

Reputation: 430

I know it is an old post, but here is another simple solution that is fast.

Public Function worksheetExists(ByVal wb As Workbook, ByVal sheetNameStr As String) As Boolean

On Error Resume Next
worksheetExists = (wb.Worksheets(sheetNameStr).Name <> "")
Err.Clear: On Error GoTo 0

End Function

Upvotes: -1

imjordy23

Reputation: 1

I came up with an easy way to do it, but I didn't create a new sub for it. Instead, I just "ran a check" within the sub I was working on. Assuming the sheet name we're looking for is "Sheet_Exist" and we just want to activate it if found:

Dim SheetCounter As Integer

SheetCounter = 1

Do Until Sheets(SheetCounter).Name = "Sheet_Exist" Or SheetCounter = Sheets.Count + 1
 SheetCounter = SheetCounter +1
Loop
If SheetCounter < Sheets.Count + 1 Then
 Sheets("Sheet_Exist").Activate
Else
 MsgBox("Worksheet ""Sheet_Exist"" was NOT found")
End If

I also added a pop-up for when the sheet doesn't exist.

Upvotes: -1

ashleedawg

Reputation: 21619

Compact `wsExists` function _{^{(without reliance on Error Handling!)}}

Here's a short & simple function that doesn't rely on error handling to determine whether a worksheet exists (and is properly declared to work in any situation!)

Function wsExists(wsName As String) As Boolean
    Dim ws: For Each ws In Sheets
    wsExists = (wsName = ws.Name): If wsExists Then Exit Function
    Next ws
End Function

Example Usage:

The following example adds a new worksheet named myNewSheet, if it doesn't already exist:

If Not wsExists("myNewSheet") Then Sheets.Add.Name = "myNewSheet"

More Information:

MSDN : For Each…Next Statement (VBA)
MSDN : Exit Statement (VBA)
MSDN : Comparison Operators (VBA)

Upvotes: 8

AOBR

Reputation: 349

I wrote this one:

Function sheetExist(sSheet As String) As Boolean
On Error Resume Next
sheetExist = (ActiveWorkbook.Sheets(sSheet).Index > 0)
End Function

Upvotes: 34

chenaou

Reputation: 1

I actually had a simple way to check if the sheet exists and then execute some instruction:

In my case I wanted to delete the sheet and then recreated the same sheet with the same name but the code was interrupted if the program was not able to delete the sheet as it was already deleted

Sub Foo ()

    Application.DisplayAlerts = False

    On Error GoTo instructions
    Sheets("NAME OF THE SHEET").Delete

    instructions:

    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "NAME OF THE SHEET"

End Sub

Upvotes: -5

MAx Segura

Reputation: 15

Without any doubt that the above function can work, I just ended up with the following code which works pretty well:

Sub Sheet_exist ()
On Error Resume Next
If Sheets("" & Range("Sheet_Name") & "") Is Nothing Then
    MsgBox "doesnt exist"
Else
    MsgBox "exist"
End if
End sub

Note: Sheets_Name is where I ask the user to input the name, so this might not be the same for you.

Upvotes: 1

gth826a

Reputation: 131

Change "Data" to whatever sheet name you're testing for...

On Error Resume Next 

Set DataSheet = Sheets("Data")

If DataSheet Is Nothing Then

     Sheets.Add(after:=ActiveSheet).Name = "Data"
     ''or whatever alternate code you want to execute''
End If

On Error GoTo 0

Upvotes: 1

Vityata

Reputation: 43575

If you are a fan of WorksheetFunction. or you work from a non-English country with a non-English Excel this is a good solution, that works:

WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1"))

Or in a function like this:

Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Not WorksheetFunction.IsErr(Evaluate("'" & sName & "'!A1"))
End Function

Upvotes: 2

Srikanta Gouda

Reputation: 521

    For Each Sheet In Worksheets
    If UCase(Sheet.Name) = "TEMP" Then
    'Your Code when the match is True
        Application.DisplayAlerts = False
        Sheet.Delete
        Application.DisplayAlerts = True
    '-----------------------------------
    End If
Next Sheet

Upvotes: 2

Shai Alon

Reputation: 990

Corrected: Without error-handling:

Function CheckIfSheetExists(SheetName As String) As Boolean
      CheckIfSheetExists = False
      For Each WS In Worksheets
        If SheetName = WS.name Then
          CheckIfSheetExists = True
          Exit Function
        End If
      Next WS
End Function

Upvotes: 18

X37V

Reputation: 49

Many years late, but I just needed to do this and didn't like any of the solutions posted... So I made one up, all thanks to the magic of (SpongeBob rainbow hands gesture) "Evaluate()"!

Evaluate("IsError(" & vSheetName & "!1:1)")

Returns TRUE if Sheet does NOT exist; FALSE if sheet DOES exist. You can substitute whatever range you like for "1:1", but I advise against using a single cell, cuz if it contains an error (eg, #N/A), it will return True.

Upvotes: 4

VirtualMichael

Reputation: 721

In case anyone wants to avoid VBA and test if a worksheet exists purely within a cell formula, it is possible using the ISREF and INDIRECT functions:

=ISREF(INDIRECT("SheetName!A1"))

This will return TRUE if the workbook contains a sheet called SheetName and FALSE otherwise.

Upvotes: 15

Shai Alon

Reputation: 990

I did another thing: delete a sheet only if it's exists - not to get an error if it doesn't:

Excel.DisplayAlerts = False 
Dim WS
For Each WS In Excel.Worksheets
    If WS.name = "Sheet2" Then
        Excel.sheets("Sheet2").Delete
        Exit For
    End If
Next
Excel.DisplayAlerts = True

Upvotes: 0

Rory

Reputation: 34045

If you are specifically interested in worksheets only, you can use a simple Evaluate call:

Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function

Upvotes: 132

ScottMcC

Reputation: 4460

Why not just use a small loop to determine whether the named worksheet exists? Say if you were looking for a Worksheet named "Sheet1" in the currently opened workbook.

Dim wb as Workbook
Dim ws as Worksheet

Set wb = ActiveWorkbook

For Each ws in wb.Worksheets

    if ws.Name = "Sheet1" then
        'Do something here
    End if

Next

Upvotes: 3

uildriks

Reputation: 51

My solution looks much like Tims but also works in case of non-worksheet sheets - charts

Public Function SheetExists(strSheetName As String, Optional wbWorkbook As Workbook) As Boolean
    If wbWorkbook Is Nothing Then Set wbWorkbook = ActiveWorkbook 'or ThisWorkbook - whichever appropriate
    Dim obj As Object
    On Error GoTo HandleError
    Set obj = wbWorkbook.Sheets(strSheetName)
    SheetExists = True
    Exit Function
HandleError:
    SheetExists = False
End Function

Upvotes: 5

Martin Carlsson

Reputation: 471

Put the test in a function and you will be able to reuse it and you have better code readability.

Do NOT use the "On Error Resume Next" since it may conflict with other part of your code.

Sub DoesTheSheetExists()
    If SheetExist("SheetName") Then
        Debug.Print "The Sheet Exists"
    Else
        Debug.Print "The Sheet Does NOT Exists"
    End If
End Sub

Function SheetExist(strSheetName As String) As Boolean
    Dim i As Integer

    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = strSheetName Then
            SheetExist = True
            Exit Function
        End If
    Next i
End Function

Upvotes: 3

M1NT

Reputation: 451

Public Function WorkSheetExists(ByVal strName As String) As Boolean
   On Error Resume Next
   WorkSheetExists = Not Worksheets(strName) Is Nothing
End Function

sub test_sheet()

 If Not WorkSheetExists("SheetName") Then
 MsgBox "Not available"
Else MsgBox "Available"
End If

End Sub

Upvotes: 2

Test or check if sheet exists

Answers (24)

Compact wsExists function (without reliance on Error Handling!)

Example Usage:

More Information:

Related Questions

Compact `wsExists` function _{^{(without reliance on Error Handling!)}}