CODEWITHSUNDEEP
vbaexcel
Daruki
Daruki

Reputation: 491

Loop through worksheets with a specific name

Say I have 10 worksheets and 4 have very similar names:

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

I want my code to find all worksheets with the text string danger in its name and execute my code

I've 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 doesn't do anything, even though there's no errors. The second code only does something if the name matches exactly with what I wrote down.

Any help is needed.

Upvotes: 5

Views: 25920

Answers (4)

Kat
Kat

Reputation: 1

Add an asterisk * before the like name. e.g. .Name Like "*danger"

Asterisk * is the wildcard for Excel

Upvotes: 0

brettdj
brettdj

Reputation: 55672

Or without looping

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

Upvotes: 1

Fadi
Fadi

Reputation: 3322

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

to this :

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

and simply you can use "danger*".

Upvotes: 3

BruceWayne
BruceWayne

Reputation: 23283

I'm pretty sure you just need to add in explicitly which worksheets you're using.

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

It's a simple fix (all I did was add ws. before each Range()), but very important when working with multiple sheets. Always be explicit when using multiple ranges. If you were using Cells(), Row(),Column(), etc. you should add the worksheet you're using as well.

An alternative layout is using With (see below for example with your SubName() routine):

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

Notice that using With allows you to just use a "placeholder" . to refer to the WS (or whatever follows With). See the line I added that uses Range(Cells(),Cells()) for a good example of this.

Edit: Using If ws.Name like "danger" Then will only run on sheets named danger (lowercase, no spaces). If you want this to work on sheets named like DangerMouse, DangerDoom, Carlos Danger, then you'll need to use ... Like "*danger*" Then

Upvotes: 6

Related Questions