Check if exists with custom code VBA

[LowRez]

Hello,

Total beginner in VBA.
I`ve been searching the forums without any luck.

First of all, let me explain what i did and what i`m trying to achieve.
I`m trying to do some sort of attendance worksheet in Excel 2019..
So far i`ve been successful following this video :

On the COPY SHEET button (min. 29:20), i`ve pasted the code in the VBA and it does work.
This is my code:


Sub Copyrenameworksheet()
'Updateby Extendoffice
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("E5").Value <> "" Then
ActiveSheet.Name = wh.Range("E5").Value
End If
wh.Activate
End Sub


What i want to do now, is that i want to check if that "E5" sheet name already exists first. If it does it should return a message, if not, create one.
Any help please?
Total noob in programming.

Thank you.

 

[ScottInTexas]

First off you defined a Worksheet variable as ws but set wh. If it didn't stop you for that then you have not made your code option explicit. You should go to Tools->Options->Require variable declaration. That way you will catch little things like your worksheet variable. Now to your question. The worksheets are a collection so you can go through the collection and check the name of the sheet. Return true if you find it false if you don't.

Public Function SheetExists(ByVal sheetName As String) As Boolean

    'Define the varaible
    Dim ws As Worksheet
    SheetExists = False
    'Start going through the collection of sheets
    For Each ws In ThisWorkbook.Sheets
        
        If ws.name = sheetName Then
            'If the sheet name matches the name passed in, then you found it.
            'Set the return to true and get out.
            SheetExists = True
            Exit For
        End If
    Next
        
End Function

 

[Fluff]

How about

Sub Copyrenameworksheet()
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   If Ws.Range("E5").Value = "" Then
      MsgBox "E5" Is blank
      Exit Sub
   End If
   If Not Evaluate("isref('" & Ws.Range("E5").Value & "'!A1)") Then
      MsgBox "sheet already exists"
      Exit Sub
   End If
   Ws.Copy After:=Worksheets(Sheets.Count)
   ActiveSheet.Name = Ws.Range("E5").Value
   Ws.Activate
End Sub

 

[LowRez]

Sub Copyrenameworksheet() Dim Ws As Worksheet Set Ws = ActiveSheet If Ws.Range("E5").Value = "" Then MsgBox "E5" Is blank Exit Sub End If If Not Evaluate("isref('" & Ws.Range("E5").Value & "'!A1)") Then MsgBox "sheet already exists" Exit Sub End If Ws.Copy After:=Worksheets(Sheets.Count) ActiveSheet.Name = Ws.Range("E5").Value Ws.Activate End Sub

Compile error:
Type mismatch...

This E5 is a drop down list containing a month.. guessing E5 is a variable that should return what`s inside that E5 cell?

 

[Fluff]

Oops, misplaced " it should be

      MsgBox "E5 Is blank"

 

[LowRez]

Oops, misplaced " it should be

      MsgBox "E5 Is blank"

Thank you,

Closer, but now with only my main sheet (named completely different), it gives me the "sheet already exists" MsgBox .

 

[Fluff]

It should be

   If Evaluate("isref('" & Ws.Range("E5").Value & "'!A1)") Then

 

[LowRez]

It should be

   If Evaluate("isref('" & Ws.Range("E5").Value & "'!A1)") Then

Thank you!

This fixed it.

 

[Fluff]

Glad we could help & thanks for the feedback.