VBA to check if a worksheet exists

jrnyman

Board Regular
Joined
Mar 10, 2002
Messages
105
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean

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

End Function
 
Last edited:
Upvote 0
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:
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!
 
Upvote 0
Possibly you have not reset ws as nothing:
Code:
        On Error Resume Next
        Set ws = Sheets(agent)

Try amending to:
Code:
        [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:
Code:
        Set ws = Nothing
        On Error Resume Next
        Set ws = Sheets(agent)
        [COLOR="Blue"]On Error GoTo 0[/COLOR]
 
Upvote 0
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:


Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,532
Messages
6,172,871
Members
452,486
Latest member
standw01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top