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

 

[MARK858]

You should have a separate function called WorksheetExists for that code to work.

 

[Logit]

This is an unconventional approach :

Sub CheckIfSheetExists()
    Dim ws As Worksheet
    Dim sheetName As String
    Dim sheetExists As Boolean
    Dim wsName
    Dim WSCount As Long
    
    ' Assign the name of the sheet you're looking for to sheetName
    sheetName = Sheets("Dates C15").Range("C15").Value
    
    ' Initialize sheetExists to False
    sheetExists = False
    
    On Error Resume Next
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet name matches sheetName
        If ws.Name = sheetName Then
            MsgBox "A worksheet with the same date and event type already exists in this workbook." & vbCrLf & vbCrLf & "If you " & _
            "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"
        
            
            Worksheets("Misc Template").Visible = False
            Worksheets("Contents").Activate

        Exit For
    
 
        End If
 
    Next ws
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = sheetName Then
        
            WSCount = Worksheets.Count
            ActiveWorkbook.Sheets("Misc Template").Copy _
            After:=ActiveWorkbook.Sheets(WSCount)
            ActiveSheet.Name = Sheets("Dates C15").Range("C15").Value
            ActiveSheet.Tab.Color = RGB(233, 113, 50)
        
        End If
    Next ws
End Sub

 

[MARK858]

Another option is to replace WorksheetExists(wsName) with Evaluate("isref('" & wsName & "'!A1)")

Just a silly question you have a line checking if a sheet doesn't exist, and an ElseIf checking if the sheet does exist, then an Else statement... what would trigger the Else statement?

 

[Pacman52]

You should have a separate function called WorksheetExists for that code to work.

Thanks for the reply Mark,

I'm really just not that knowledgeable on VBA and to be honest I's surprised I've got as far as I had with this code, most of which was put together by many searches on google and YouTube.

I did find loads of code that did exactly what I'm after BUT all of it required the user to enter the sheet name they wanted to check to see if it existed to be entered into an input box first and then it could be checked. The issue with this for me is by using that method the user would have to know exactly how a newly copied sheet has to be named and what 'category' it needs to be in (or it could just be a case of I don't really understand it enough - which is more than likely).

As to using a function some of what I tried did actually have a function but again it also had an Input box and when I tried to adapt the code to suit my needs it errored out for various reasons.

If you think this is the way forward I'm more than happy to learn and try it again with a function though so I will do some more research and se what comes up.

Paul

 

[Pacman52]

This is an unconventional approach :

Sub CheckIfSheetExists()
    Dim ws As Worksheet
    Dim sheetName As String
    Dim sheetExists As Boolean
    Dim wsName
    Dim WSCount As Long
   
    ' Assign the name of the sheet you're looking for to sheetName
    sheetName = Sheets("Dates C15").Range("C15").Value
   
    ' Initialize sheetExists to False
    sheetExists = False
   
    On Error Resume Next
   
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet name matches sheetName
        If ws.Name = sheetName Then
            MsgBox "A worksheet with the same date and event type already exists in this workbook." & vbCrLf & vbCrLf & "If you " & _
            "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"
       
           
            Worksheets("Misc Template").Visible = False
            Worksheets("Contents").Activate

        Exit For
   
 
        End If
 
    Next ws
   
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = sheetName Then
       
            WSCount = Worksheets.Count
            ActiveWorkbook.Sheets("Misc Template").Copy _
            After:=ActiveWorkbook.Sheets(WSCount)
            ActiveSheet.Name = Sheets("Dates C15").Range("C15").Value
            ActiveSheet.Tab.Color = RGB(233, 113, 50)
       
        End If
    Next ws
End Sub

Thanks also for your reply Logit,

I did try your code and it errored out on the SheetName. I noticed you put 'Dates C15"' as the actual name and I'm assuming that is just as an example?

  sheetName = Sheets("Dates C15").Range("C15").Value

 

[Pacman52]

Just a silly question you have a line checking if a sheet doesn't exist, and an ElseIf checking if the sheet does exist, then an Else statement... what would trigger the Else statement?

To be honest I have no idea but after many attempts of trying to get the sub to work this was the only way it would.

In my naivety, I though to myself great now all I have to do is copy the sub to each of the other 4 categories, adjust any sheet names and rows etc but that is where I hit the problems.

 

[MARK858]

If you think this is the way forward I'm more than happy to learn and try it again with a function

Not saying it is the way forward, I'm saying that code is designed to run with a separate function, the line I posted doesn't require a separate function.

 

[mumps]

See if this works for you:

[
Sub NewMiscellaneous()
    Application.ScreenUpdating = False
    Dim wsName As String
    wsName = Sheets("Dates").Range("C15").Value
    If Not Evaluate("isref('" & wsName & "'!A1)") Then
        Sheets("Misc Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = wsName
        ActiveSheet.Tab.Color = RGB(0, 176, 80)
    Else
        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
    End If
    Application.ScreenUpdating = True
End Sub
/CODE]

 

[Logit]

' Assign the name of the sheet you're looking for to sheetName
    sheetName = Sheets("Dates C15").Range("C15").Value

The new sheet name is derived from the value located in Cell C15 on Sheet 'Dates C15'. The new sheet name will be named for whatever
is entered in C15.

This line of code was taken directly from your original posting.

The code should not error out unless the data in cell C15 is the same as an already existing sheet. Even then Excel should name the new sheet with a number
after the name. For example in this scenario the new sheet should be named 'Dates C15(2)'. Were it to be run again, the second new sheet would be named
'Dates C15(3)', etc. etc.