Test or check if sheet exists

Question

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

User · Answer

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

User · Answer

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

User · Answer

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     amp  Range  Sheet Name    amp      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

User · Answer

My solution looks much like Tims but also works in case of non-worksheet sheets - charts  Public nbsp Function nbsp SheetExists strSheetName nbsp As nbsp String  nbsp Optional nbsp wbWorkbook nbsp As nbsp Workbook  nbsp As nbsp Boolean  nbsp  nbsp  nbsp  nbsp If nbsp wbWorkbook nbsp Is nbsp Nothing nbsp Then nbsp Set nbsp wbWorkbook nbsp   nbsp ActiveWorkbook nbsp  or ThisWorkbook - whichever appropriate  nbsp  nbsp  nbsp  nbsp Dim nbsp obj nbsp As nbsp Object  nbsp  nbsp  nbsp  nbsp On nbsp Error nbsp GoTo nbsp HandleError  nbsp  nbsp  nbsp  nbsp Set nbsp obj nbsp   nbsp wbWorkbook Sheets strSheetName   nbsp  nbsp  nbsp  nbsp SheetExists nbsp   nbsp True  nbsp  nbsp  nbsp  nbsp Exit nbsp Function HandleError   nbsp  nbsp  nbsp  nbsp SheetExists nbsp   nbsp False End nbsp Function

User · Answer

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 Sheets shtName      On Error GoTo 0     WorksheetExists   Not sht Is Nothing End Function

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt      Err Clear  On Error GoTo 0  End Function

User · Answer

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    amp  vSheetName  amp    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

User · Answer

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      amp  wsName  amp     A1      Or in a function like this   Function WorksheetExists sName As String  As Boolean     WorksheetExists   Not WorksheetFunction IsErr Evaluate      amp  sName  amp     A1    End Function

User · Answer

I wrote this one   Function sheetExist sSheet As String  As Boolean On Error Resume Next sheetExist    ActiveWorkbook Sheets sSheet  Index  gt  0  End Function

User · Answer

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   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

User · Answer

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  lt  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

User · Answer

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

User · Answer

Compact wsExists function  without reliance on Error Handling    Here s a short  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     amp  sName  amp     A1    End Function

User · Answer

Short and clean   Function IsSheet n   As Boolean     IsSheet   Not IsError Evaluate n  amp    a1    End Function

User · Answer

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

User · Answer

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

[excel] Test or check if sheet exists

Examples related to excel

Examples related to vba

Examples related to scripting