Test or check if sheet exists

user_843818 0
Viewed 634227
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
vba excel scripting
23 Answers

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:

user_81127760

Short and clean:

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

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
user_85139760

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.

user_99424580

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