VBA to check if a worksheet exists

[jrnyman]

Does anyone know the code for checking if a worksheet exists. The worksheets name will be the String "NewSht" followed by either the letter "L" or "B". I'm trying to check if the sheet exists, and if it does, overwrite all its cells. If it doesn't, I need to create a new sheet with that name. Thanks for the help.

 

[Dave Hawley]

Hi

If you are going to overwrite them anyway, Just use this:

Application.DisplayAlerts = False
On Error Resume Next
Sheets("NewShtL").Delete
Sheets("NewShtB").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add().Name = "NewSht"


But to check if sheet exists you would use

Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets("NewShtL")
On Error GoTo 0
If Not wsSheet Is Nothing Then
MsgBox "I do exist"
Else
MsgBox "I do NOT exist"
End If

 

[Colo]

Hi. The following code is using Loop.
Please try. Regards,

Sub Test()
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "NewSht*" Then flg = True: Exit For
Next
If flg = True Then
MsgBox "Found!"
Else
Sheets.Add.Name = "NewSht"
End If
End Sub

 

[chazz]

very clever.

setting the non-existent sheet as an object and then blowing through the errors is clever indeed.

also learned from the use of wildcard * in the second suggestion.

 

[Spencer Jobe]

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean

On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function

 

[High Plains Grifter]

I am having problems implementing the code to check whether a sheet already exists in my own macro - for some reason, the following code always seems to think that the new sheet name already exists, and asks whether I would like to overwrite it, even when the sheet in question patently does not exist. Below is a section of the macro, containing the relevant code:

Sub NewSheets()
Dim agent As String
Dim lastone As String
Dim i As Integer
Dim max As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
max = InputBox("How Many Rows down the list do you wish to use?", "Enter a number to create that number of sheets")
For i = 1 To max
    'for the first of the new sheets, the previous sheet is called template, by definition
    If i = 1 Then
            lastone = "Template"
    'otherwise, the last previous sheet name will be one higher in the list than the current
    Else
            lastone = Worksheets("Lists").Range("A1").Offset(i - 1, 0).Value
    End If
        'get the agent name
        agent = Worksheets("Lists").Range("A1").Offset(i, 0).Value
        'check whether a sheet with that name already exists
        On Error Resume Next
        Set ws = Sheets(agent)
    'if none with that name, create a new sheet
    If ws Is Nothing Then
            ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
            ActiveSheet.Name = agent
    'if the sheet already exists, ask whether the user wants to replace it
    Else
            overW = MsgBox("This sheet already exists - Overwrite?", vbYesNoCancel, agent)
        If overW = vbYes Then
                Sheets(agent).Delete
                ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
                ActiveSheet.Name = agent
        ElseIf overW = vbNo Then
                GoTo Skip
        ElseIf overW = vbCancel Then
                Exit Sub
        End If
    End If

I cannot work out which part I have got wrong - can anyone help?

Thanks for looking at the post, and all replies are much appreciated!

 

[xenou]

Possibly you have not reset ws as nothing:

        On Error Resume Next
        Set ws = Sheets(agent)

Try amending to:

        [COLOR="Blue"]Set ws = Nothing[/COLOR]
        On Error Resume Next
        Set ws = Sheets(agent)

Also not that error handling will be left "on" and may mask other problems now. So also preferable is:

        Set ws = Nothing
        On Error Resume Next
        Set ws = Sheets(agent)
        [COLOR="Blue"]On Error GoTo 0[/COLOR]

 

[High Plains Grifter]

Thanks

Thanks - worked perfectly. I will look up what "on error goto 0" actually does and thus try to avoid that in the future.

 

[Lil Smiles]

Hey Guys,

I saw this thread and I wanted to show the code I came up with.

I don't like "On Error Resume Next" command so I made the following function:

Sub Testing()
    Dim SheetName1, SheetName2 As String
    Dim Result As Boolean
    Dim i As Long
    
    
    SheetName = Array("laskgfasdfalskg", "Config")
    
    For i = 0 To UBound(SheetName)
        Result = WorksheetExists(SheetName(i))
        If Result = False Then
            MsgBox "Sheet name " & SheetName(i) & " doesn't exist!"
        Else
            MsgBox "Sheet name " & SheetName(i) & " does exist!"
        End If
    Next i
    
    
End Sub



Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
    
    Dim Sht As Worksheet
        
    WorksheetExists = False
        
    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name = WorksheetName Then worksheetexits = True
    Next Sht
    
End Function

 

[canesbr]

Here is a working version

Function WorksheetExists(ByVal WorksheetName As String) As Boolean
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
WorksheetExists = True
Exit Function
End If
Next Sht
WorksheetExists = False
End Function