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

MagnusE

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,071
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're missing .Name
Rich (BB code):
 For Each ws In Worksheets
        If ActiveCell.Value = ws.Name Then
        Exit Sub
        End If
    Next ws
or can do
VBA Code:
    If ActiveCell.Value <> "" Then
        If Evaluate("ISREF('" & ActiveCell.Value & "'!A1)") Then Exit Sub
    End If
which doesn't need a loop.
 
Solution

MagnusE

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
You're missing .Name
Rich (BB code):
 For Each ws In Worksheets
        If ActiveCell.Value = ws.Name Then
        Exit Sub
        End If
    Next ws
or can do
VBA Code:
    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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,071
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top