Macro To Run on certain sheets

[Hobbit]

To the forum

Hi there! Hope you are all well. I have a query about running the same macro over a certain number of worksheets.

The macro exists on some (but not every) worksheets within the workbook. If there are 5 worksheets which fall into that category, I have to enter each one and then run the macro on that sheet.

Is there a way for me to:
- get a macro to cycle through all the worksheets, and
- if the tab name contains a key word, then run the macro?
- if not, skip?

Many many thanks in advance to all!

 

[KennyGreens]

Hi there, try this:

Dim i As Integer
Dim findStr As String
Dim TabCount As Long

TabCount = ThisWorkbook.Worksheets.Count

findStr = “keyword” ‘put keyword to search for

For i = 1 To TabCount
If InStr(Sheets(i).name, findStr) > 0 Then
‘Do macro
End If
Next

 

[Fluff]

Another option

Sub HideSheets()
   Dim Ws As Worksheet
   
   For Each Ws In Sheets(Array("[COLOR=#ff0000]Pcode[/COLOR]", "[COLOR=#ff0000]MA[/COLOR]", "[COLOR=#ff0000]Fluff[/COLOR]"))
      ' Your code here
   Next Ws
End Sub

Change the values in red to the sheet names you want to run the macro on

 

[craigexcel]

Another option, using "Like"

Sub FindSheets()

Dim ws          As Worksheet
Dim sKeyString  As String

sKeyString = "Sheet"

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*" & sKeyString & "*" Then
        'run your macro
    Else
    End If
Next ws
    
End Sub

And if the key word you're looking for, in each sheet name, could change, you can add an InputBox at the top of the code, to get the "sKeyString" value dynamically.

 

[Hobbit]

Oh my!!!

Thank you all for such quick and fantastic solutions!
@craigexcel
Quick question: I like the InputBox option. How do I add that on?

Thanks!

 

[Hobbit]

I tested out the solutions. I think I must be doing something wrong. It executes the macro perfectly, but on the sheet I am looking at when I run it. Not on the sheets with the defined text.

The original macro does not define the worksheet, only the columns to execute it on. I used to run them per sheet. Do I need to modify this to say run on sheet that it is cycling through?

Many thanks again!

 

[Fluff]

Could you please supply your code, without it it's very difficult to figure out what's wrong.

 

[craigexcel]

Hi Hobbit,
I believe the solution I provided does what you had asked in your original post. Have you tried my version? It loops through every worksheet within the workbook, and if the sheet's name contains the text you hardcode @ "sKeyString = "Sheet" ", the code that you need to add at "run your macro", would execute.

As for the 'InputBox' component, you could use something like:

sKeyString = InputBox("Enter the part of the sheet name you want to search for:", _
    "Enter sheet string")

If you opt to use this, you would replace "sKeyString = "Sheet", with the above line of code, making that a dynamic variable, instead of being hardcoded.

 

[Hobbit]

Hi there

At the moment, my code is as such:

Sub FindSheets()

Dim ws          As Worksheet
Dim sKeyString  As String

sKeyString = "Option"

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*" & sKeyString & "*" Then
            Range("AZ:BA,BG:BH,BJ:BJ,AL:AL").Select
            Selection.Delete Shift:=xlToLeft
    Else
    End If
Next ws
    
End Sub

I was hoping that when I ran FindSheets, it would find the worksheets with the word "Option" in it, and then delete those columns on the worksheets with the word "Option".

At the moment, when I run it, it deletes the columns on the sheet that I have it on, regardless of whether or not "Option" is a word in the tab.

Hobbit

 

[MARK858]

Maybe...

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*" & sKeyString & "*" Then
            [COLOR="#FF0000"]ws.[/COLOR]Range("AZ:BA,BG:BH,BJ:BJ,AL:AL").Delete Shift:=xlToLeft
    End If
Next ws

or as you are just doing a single test...

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*" & sKeyString & "*" Then [COLOR="#FF0000"]ws.[/COLOR]Range("AZ:BA,BG:BH,BJ:BJ,AL:AL").Delete Shift:=xlToLeft
Next ws