VBA Macro to check if sheet name exists


Try this :

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each sheet In Worksheets
        If sheetToFind = sheet.name Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
End Function

And use like this :

if sheetExists("TEMPLATE") = true then
  'your code
end if

Excel VBA If WorkSheet("wsName") Exists

Author by


Updated on December 05, 2022


  • Nikolajs
    Nikolajs over 1 year

    I'm currently running a macro that copies a template sheet, takes user's inputs, renames the sheet, and places the user inputs in the sheet.

    Everything seems to be working except for the function that checks if the Sheet name already exists. The actual "Sheet already exists" works, however, before prompting me with this error it duplicates the TEMPLATE worksheet for some reason.

    Here's the code for the actual macro (affected area is: Sheets("TEMPLATE").Copy After:=Sheets("TEMPLATE"))

    'Button to load new GSA Form
    Private Sub CommandButton1_Click()
    Const cstrTitle As String = "Create a new GSA worksheet"
    Const cstrPrompt As String = "Enter the GSA number for the new worksheet"
    Dim projName As String
    Dim projAddress As String
    Dim projDate As Date
    Dim strInput As Variant                                     'Input value from user (GSA Number)
    Dim strDefault As String: strDefault = ""                   'Sets default value for inputbox
    Dim strInputErrorMessage As String                          'Error message
    Dim booValidateOK As Boolean: booValidateOK = False
    On Error GoTo HandleError
        strInput = ActiveSheet.Range("C9").Value
        projName = ActiveSheet.Range("C6").Value
        projAddress = ActiveSheet.Range("C7").Value
        projDate = ActiveSheet.Range("C8").Value
        If Len(strInput) = 0 Then GoTo HandleExit
        GoSub ValidateInput
        If Not booValidateOK Then
            If vbCancel = MsgBox(strInputErrorMessage & "Retry?", vbExclamation + vbOKCancel) Then GoTo HandleExit
        End If
    Loop While Not booValidateOK
    Sheets("TEMPLATE").Copy After:=Sheets("TEMPLATE")                          'Copy Template Sheet, places the copy after the template sheet
    ActiveSheet.Name = strInput                                                 'Renames the new sheet to the user's input
    ActiveSheet.Range("C5").Value = projName                                    'Inputs Project Name to new sheet
    ActiveSheet.Range("C6").Value = projAddress                                 'Inputs Project Address to new sheet
    ActiveSheet.Range("C7").Value = projDate                                    'Inputs Project Date to new sheet
    ActiveSheet.Range("C8").Value = strInput                                    'Inputs GSA # to new sheet
    ThisWorkbook.Worksheets("MASTER").Range("C6").Value = ""   'name'           'Deletes inputs from MASTER sheet
    ThisWorkbook.Worksheets("MASTER").Range("C7").Value = ""   'address'
    ThisWorkbook.Worksheets("MASTER").Range("C8").Value = ""   'date'
    ThisWorkbook.Worksheets("MASTER").Range("C9").Value = ""   'gsa #'
    Exit Sub
    MsgBox Err.Description
    Resume HandleExit
    If SheetExists(strSheetName:=strInput) Then
        strInputErrorMessage = "Sheet already exists. "
        booValidateOK = True
    End If
    End Sub

    Here's the function that checks to see if the sheet already exists

    Public Function SheetExists(strSheetName As Variant, 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
     SheetExists = False
    End Function
    • BigBen
      BigBen almost 6 years
      I'm assuming the plan is to get strInput from an InputBox and you haven't added that functionality yet? As is the user has no way to really retry...
    • Nikolajs
      Nikolajs almost 6 years
      Sorry, I didn't specify. The strInput is taken from a cell in the worksheet
    • BigBen
      BigBen almost 6 years
      I saw that. My point being that you never give the user the option to change that value if the sheet exists, or if the cell is blank - so why loop?
  • Nikolajs
    Nikolajs almost 6 years
    When debugging this, it goes through the for loop, but it never sets the "SheetExists = True"
  • Customizer
    Customizer almost 6 years
    I would suggest using StrComp with 1 for the last parameter to avoid any case sensitivity with the comparison.