Mark a cell and loop through Worksheets and search if Worksheet already exists with same name as cell value

[MagnusE]

Hello again!

As the header shows, I need a code that's checking my ActiveCell.value (which is chosen in different cells and different Worksheets) and look through all Worksheets for already existing Worksheets with the same name as the marked cell. This is the issue which I don't have a solution for yet.

The other code is working fine.
-Check if cell is empty, and giving me a Msgbox
-Copy a Worksheet and rename the new with cell value
-Change the color of the Worksheet

Sub CopySheetVAC()

    Dim shtName As String
    Dim ws As Worksheet
       
    If ActiveCell.Value = Empty Then
    MsgBox ("No WBS in Cell")
    Exit Sub
    End If

   'From here is where the code is fault
    For Each ws In Worksheets
        If ActiveCell.Value = ws Then
        Exit Sub
        End If
    Next ws

    'From here all is fine
    shtName = ActiveCell.Value
    Sheets("TEMP VAC").Visible = True
    Sheets("TEMP VAC").Select
    Sheets("TEMP VAC").Copy before:=Sheets(8)
    Sheets("TEMP VAC (2)").Select
    Sheets("TEMP VAC (2)").Name = shtName
    With ActiveWorkbook.Sheets(shtName).Tab
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599993896298105
    End With

    Sheets("TEMP VAC").Visible = False
End Sub

Grateful for all the help I can get

 

[MARK858]

You're missing .Name

 For Each ws In Worksheets
        If ActiveCell.Value = ws.Name Then
        Exit Sub
        End If
    Next ws

or can do

    If ActiveCell.Value <> "" Then
        If Evaluate("ISREF('" & ActiveCell.Value & "'!A1)") Then Exit Sub
    End If

which doesn't need a loop.

 

[MagnusE]

You're missing .Name

 For Each ws In Worksheets
        If ActiveCell.Value = ws.Name Then
        Exit Sub
        End If
    Next ws

or can do

    If ActiveCell.Value <> "" Then
        If Evaluate("ISREF('" & ActiveCell.Value & "'!A1)") Then Exit Sub
    End If

which doesn't need a loop.

Hello Mark

I tried the first option and worked like a charm. Sometimes its so easy but yet so difficult to see whats missing
Thanks a bunch!

 

[MARK858]

You're welcome