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!

 

[Hobbit]

:'(

Unfortunately, adding ws in front of it does the same thing which is to delete the columns on the sheet would have open rather than the sheets with the "Option".

But thank you for trying!

 

[MARK858]

:'(

Unfortunately, adding ws in front of it does the same thing which is to delete the columns on the sheet would have open rather than the sheets with the "Option".

But thank you for trying!

You must have something you haven't told us then as I just numbered the cells for 10 rows from A1 to CA10 on 7 sheets, renamed 3 of the sheets so their name contained the word Option and ran the code below...

Sub FindSheets()

    Dim ws As Worksheet, sKeyString As String

    sKeyString = "Option"

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

End Sub

It only deleted the columns on the 3 sheets with the word Option in the name (and did not touch the Activesheet that didn't have the word Option in the sheet name). Please note that it is case sensitive.

 

[Hobbit]

@MARK858
Interestingly, I just started a whole new workbook for the working template instead of building more on the existing one, and it worked! I really am not sure what happened in the old workbook. :S

That means that the end process is going to be so much easier for me to process now.

Having that in mind, would it be possible to call another macro if it fits the same profile?

Sub FindSheets()

    Dim ws As Worksheet, sKeyString As String

    sKeyString = "Option"

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "*" & sKeyString & "*" Then Application.Run "FillDown"
    Next ws

End Sub

Sub FillDown()
    Range("GA2:GS2").Select
    Selection.AutoFill Destination:=Range("BA2:BS211"), Type:=xlFillDefault
    Range("GA2:GN211").Select
    Columns("GD:GC").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.EntireColumn.Hidden = True
    Columns("JA:JA").Select
    Selection.Delete Shift:=xlToLeft
End Sub

Yes, it's an exceptionally unwieldly document that no one wants to really move away from. It is an absolute frankenstein to work with so you can imagine how happy I am if we can automate that part of the process.

 

[MARK858]

Having that in mind, would it be possible to call another macro if it fits the same profile?

First of all I don't know what you mean by "fits the same profile" but anyway a few remarks...

1. You don't need Application.run if the Sub's are in the same workbook, you can just call the 2nd Sub directly.

2. The Filldown sub is written to work on the activesheet not the ws sheet and although there are ways to pass the ws to the 2nd sub it would be a lot easier if it was incorporated in the same sub (after a bit of tidying up). Is there any reason why it can't be in the same sub?

3. I will be amazed if you aren't getting an "Autofill method of Range class failed" error on the

Selection.AutoFill Destination:=Range("BA2:BS211"), Type:=xlFillDefault

line with the code you posted going by your previous selection.

4. I am a bit confused why you would change the font on Columns("GD:GC") then hide them?

 

[Hobbit]

I did not know how to call the second sub to be honest. I googled the query last year and saw the Application.Run command and when I tried it and it worked, I thought that was the way to do it.

The template has multiple rows. In the initial cut, we remove certain rows, then we do fill down to maintain the formulas and calculations to take into account only the relevant (whatever is left over) rows. At least that is my understanding of what that does.

Then the workbook gets passed on to other people for completion before it returns to me to extract. At that point I have to remove the commercial in confidence materials (what FindSheets has done) prior to passing it onto clients without other sensitive data. So some bits are left as is, some bits are deleted.

The font issue - oh that is a legacy thing. First they wanted it in a certain format so they could fill in the cells to then get the result, then it became they wanted to hide it and then they wanted BOTH options (for different people!). I'm trying a one size fits all option.

 

[MARK858]

Untested....

Please note that it assumes your autofill should be Range("GA2:GS211").... Change to suit.

Sub FindSheets()

    Dim ws As Worksheet, sKeyString As String
    Dim myAns1 As Long, myAns2 As Long, myAns3 As Long
    Dim myAns4 As Long, myAns5 As Long

    sKeyString = "Option"

    myAns1 = MsgBox("Do you want to delete the Columns", vbQuestion + vbYesNo, "Delete the Columns")
    If myAns1 = vbYes Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "*" & sKeyString & "*" Then ws.Range("AZ:BA,BG:BH,BJ:BJ,AL:AL").Delete Shift:=xlToLeft
        Next ws
    End If


    myAns2 = MsgBox("Do you want to Autofill the range?", vbQuestion + vbYesNo, "Autofill the range")
    If myAns2 = vbYes Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "*" & sKeyString & "*" Then ws.Range("GA2:GS2").AutoFill ws.Range("GA2:GS211")
        Next ws
    End If

    myAns3 = MsgBox("Do you want to change the font?", vbQuestion + vbYesNo, "Change the font")
    If myAns3 = vbYes Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "*" & sKeyString & "*" Then
                With ws.Columns("GC:GD").Font
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                End With
            End If
        Next ws
    End If

    myAns4 = MsgBox("Do you want to hide the columns?", vbQuestion + vbYesNo, "Hide the columns")
    If myAns4 = vbYes Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "*" & sKeyString & "*" Then ws.Columns("GC:GD").Hidden = True
        Next ws
    End If

    myAns5 = MsgBox("Do you want to delete column JA?", vbQuestion + vbYesNo, "Delete column JA")
    If myAns5 = vbYes Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "*" & sKeyString & "*" Then ws.Columns("JA:JA").Delete Shift:=xlToLeft
        Next ws
    End If


End Sub

 

[Hobbit]

@MARK858
That is the bees knees. The solution. Oh wow. Thank you thank you thank you!! This has made my job so much easier!

Just another quick question while I am trying to learn something here - you mentioned that it was not necessary to use "Application.Run" if the macro was within the same workbook. How should I call up the other macro then? (Although it is no longer relevant in this instance?)

Many thanks again!!

I owe you a beer!

 

[MARK858]

you mentioned that it was not necessary to use "Application.Run" if the macro was within the same workbook. How should I call up the other macro then? (Although it is no longer relevant in this instance?)

Call FillDown

or just

FillDown

If I remember right you have to use the word Call if there are arguments in the Sub statement/line.