Loop through worksheets with a specific name

Question

Let's say I have 10 sheets and 4 have very similar names:

1.danger tom
Man 2.danger
3.danger ten
4.danger lan

I want my code to detect all sheets with a text string threat in its name and execute my code

I tried the following two instances:

Public Sub SubName()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    If ws.Name Like "danger" Then
       *Also tried this version: If ws.Name Like "danger" = True Then*

        Range("A1").Interior.ColorIndex = 37
    End If

Next ws

End Sub 

take 2

Sub WorksheetLoop()

Dim ws  As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If InStr("danger", ws.Name) > 0 Then
        Range("A1").Interior.ColorIndex = 37
    End If
Next ws

End Sub

The first code does nothing, although there are no errors. The second code only does something if the name matches what I wrote down.

Any help needed.

Answer 1

I think your first code will work if you change this:

 If ws.Name Like "danger" Then
   *Also tried this version: If ws.Name Like "danger" = True Then*

    Range("A1").Interior.ColorIndex = 37
End If

:

If ws.Name Like "danger" & "*" Then    
     ws.Range("A1").Interior.ColorIndex = 37
End If

and you can just use the "danger".

Answer 2

, , .

Public Sub SubName()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "danger" Then
      ' *Also tried this version: If ws.Name Like "danger" = True Then*
        ws.Range("A1").Interior.ColorIndex = 37
    End If
Next ws

End Sub

Sub WorksheetLoop()

Dim ws  As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If InStr("danger", ws.Name) > 0 Then
        ws.Range("A1").Interior.ColorIndex = 37
    End If
Next ws

End Sub

(, , ws. Range()), . . Cells(), Row(), Column() .., , .

With (. , , SubName()):

Public Sub SubName()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    With ws
    If .Name Like "danger" Then
      ' *Also tried this version: If ws.Name Like "danger" = True Then*
        .Range("A1").Interior.ColorIndex = 37
      ' For illusatration, this will color the range A1:B10
      ' .Range(.Cells(1,1),.Cells(10,2)).Interior.ColorIndex = 37
    End If
    End with
Next ws

End Sub

, With "placeholder" . WS ( - With). . , , Range(Cells(),Cells()) .

: If ws.Name like "danger" Then danger (, ). , DangerMouse, DangerDoom, Carlos Danger, ... Like "*danger*" Then

Answer 3

Or without a loop

Sub GetSheets()
Dim strIn As String
Dim X, xArr

strIn = Application.InputBox("Search string", "Enter string to find", "*List*", , , , , 2)
If strIn = "False" Then Exit Sub

ActiveWorkbook.Names.Add "shtNames", "=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND(""]"",GET.WORKBOOK(1)))"
X = Filter([index(shtNames,)], strIn, True, 1)

For Each xArr In X
Sheets(xArr).Range("A1").Interior.ColorIndex = 37
Next

End Sub

Answer 4

Add an asterisk *in front of the name like. eg.Name Like "*danger"

Asterisk *is a template for Excel