Checking for Worksheets that already exist

[Pacman52]

Hi all, I'm trying (without success) to check is a worksheet already exists in a workbook. If it does exist to show a msgbox and if it doesn't exist to copy and rename it and place it as the very last sheet in the workbook.

The workbook has 5 worksheets e.g. WS1, WS2, ect all hidden on open of the workbook

The 'flow' of the workbook is a user clicks on a button on the menu to create a new event
Dependant on which button they selected a specific userform opens with either 2 textboxes for a name and date or just one textbox for a date.
The date values are then written to a worksheet called 'Dates' onto the relevant referenced row and concatenated with a value already in a different cell on the same row.
The concatenated value is then used as the newly copied worksheet name.

Once they click the OK button on the userform the following code checks if the worksheet they want to create already exists, If the worksheet doesn't exist then it copies it and renames it.
If the worksheet does already exist then the msgbox fires.

I've managed to get everything working to a point on one of the buttons using the code below, but when I copied the code (changing the relevant references) to another buttons sub routine, clicked the button to create a different worksheet it kept firing the msgbox even though the worksheet did not already exist in the workbook.

So basically I've tried everything I can to work out why and finally given up as I really don't know enough about VBA to understand what is causing the issue.

Could anyone let me know how I can get this to work please so I can finally finish this workbook.

Many thanks in advance Paul

Sub NewMiscellaneous() 'This name of this sub is different for each of the 5 OK buttons originally clicked  on each userform
Dim WSCount As Long
Dim wsName As String
wsName = Sheets("Dates").Range("C15").Value ' The range reference is either  C3, , C6, C9, C12 or C15

    If Not WorksheetExists(wsName) Then
 
        WSCount = Worksheets.Count
        ActiveWorkbook.Sheets("Misc Template").Copy _
        After:=ActiveWorkbook.Sheets(WSCount)
        ActiveSheet.Name = Sheets("Dates").Range("C15").Value
        ActiveSheet.Tab.Color = RGB(0, 176, 80)
 
    ElseIf WorksheetExists(wsName) Then
 
        MsgBox "A worksheet with the same date and event type already exists in this workbook." & vbCrLf & vbCrLf & _
        "If you want to go to the original worksheet to make any changes, please select '" & wsName & "' from the Contents list." & vbCrLf & vbCrLf & _
        "Otherwise, check the date you are creating a new worksheet for and try again from the menu.", vbExclamation + vbOKOnly, "Worksheet Exists"
     
    Call Contents
     
        Worksheets("Misc Template").Visible = False
        Worksheets("Contents").Activate
 
    Else
     
        WSCount = Worksheets.Count
        ActiveWorkbook.Sheets("Misc Template").Copy _
        After:=ActiveWorkbook.Sheets(WSCount)
        ActiveSheet.Name = Sheets("Dates").Range("C15").Value
        ActiveSheet.Tab.Color = RGB(233, 113, 50)
 
    End If
 
End Sub

 

[Pacman52]

Hi Logit and Mark,

Just to update you both after a bit of a trawl of the internet and making use of Google AI. I managed to put the sub below together and after testing multiple times on each of the 5 categories it works perfectly for what I'm after.

Ultimately the workbook is being built for my wife in the hope it makes her business record keeping easier which in turn will make my life easier when I come to do her monthly accounts. My reasoning behind having this check is to stop the her ever creating a duplicate sheet and thus 'forcing her to look for a sheet she has already created and forgotten about.

Thank you both so much for your replies though and I'm sorry if I wasted your time in replying.

Paul

Sub Test1()
Dim wsSource As Worksheet
Dim wsNew As Worksheet
Dim newSheetName As String
Dim wsName

    Set wsSource = ThisWorkbook.Worksheets("Test Template")

        newSheetName = Sheets("Dates").Range("C3").Value

    On Error Resume Next
    
    Set wsNew = ThisWorkbook.Worksheets(newSheetName)
    
    On Error GoTo 0

    If Not wsNew Is Nothing Then
    
        MsgBox "A worksheet with the same date and event type already exists in this workbook." & vbCrLf & vbCrLf & _
        "If you want to go to the original worksheet to make any changes, please select '" & newSheetName & "' from the worksheets in the Contents list." & vbCrLf & vbCrLf & _
        "Otherwise, check the date you are creating a new worksheet for and try again from the menu.", vbExclamation + vbOKOnly, "Worksheet Exists"
        
        ThisWorkbook.Sheets("Contents").Activate
        
    Exit Sub
    
    End If

        wsSource.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        ActiveSheet.Name = newSheetName
        Worksheets("Test Template").Visible = False
    
End Sub

 

[Logit]

No worries. Glad you have a solution.