Running one macro in several sheets by a code

[Reza1001]

Hi dears

i have a file including 30 sheets . in some sheets i have one rectangle ( named "100" ) which must be hidden/unhidden and a rounded rectangle ( named "200" ) which must be colorized . i have written two macros for this matter . one of them is this :

Sub ProtBUDCOK()
'
' ProtBUDCOK Macro
'
'
ActiveSheet.Shapes.Range(Array("Rectangle: Rounded Corners 200")).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(187, 170, 43)
.Transparency = 0
.Solid

End With

Range("s1").Select
ActiveSheet.Shapes.Range(Array("Rectangle 100")).Select
ActiveSheet.Shapes.Range(Array("Rectangle 100")).Visible = msoFalse
End Sub


i want to run this macro and other one by bottoms in sheet1 for sheet2,sheet5,sheet6 and sheet9 ( with codenames ) . below code doesn't work maybe because of ActiveSheet part of above macro or wrong addressing in sheet names . please help me.

Sub RunOnAllSheets()

Application.ScreenUpdating = False

For Each Worksheet In ThisWorkbook.Worksheets
Debug.Print wrkSht.Name
Sheets(Array("Sheet2", "Sheet5", "Sheet6", "Sheet9")).Select

Call ProtBUDBOK
Next
Application.ScreenUpdating = True

End Sub

 

[Reza1001]

Try...

Sub ProtBUDCOK()

    Dim i As Integer, myArr As Variant, ws As Worksheet
    myArr = Array("Sheet2", "Sheet5", "Sheet6", "Sheet9")


    For Each ws In ActiveWorkbook.Worksheets
       
        For i = LBound(myArr) To UBound(myArr)
           
            If ws.CodeName = myArr(i) Then
               
               
                    With ws.Shapes("200").Fill
                        .Visible = msoTrue
                        .ForeColor.RGB = RGB(187, 170, 43)
                        .Transparency = 0
                        .Solid
                    End With
             

                ws.Shapes("100").Visible = msoTrue

            End If
       
        Next i
   
    Next ws

End Sub

Edit: done away with an unnecessary "With" statement

really appreciated . this code works perfectly .

 

[Reza1001]

Hi
untested but try this update to your code & see if does what you want

Sub ProtBUDCOK()
   
    Dim sh          As Variant
   
    For Each sh In Array(Sheet2, Sheet5, Sheet6, Sheet9)
       
        With sh.Shapes("Rectangle: Rounded Corners 200").Fill
           
            .Visible = msoTrue
            .ForeColor.RGB = RGB(187, 170, 43)
            .Transparency = 0
            .Solid
           
        End With
       
        sh.Shapes("Rectangle 100").Visible = msoFalse
       
    Next sh
   
End Sub

Dave

thanks a lot . works fine

 

[MARK858]

really appreciated . this code works perfectly .

Happy it helped and welcome to the board, but I'd go with Dave's code in post 9 as it is a bit shorter