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

 

[kvsrinivasamurthy]

I have done minimum changes in the code .Try on a copy of file.

Sub RunOnAllSheets()
Dim Sh
Application.ScreenUpdating = False

For Each Sh In Array("Sheet2", "Sheet5", "Sheet6", "Sheet9")
Debug.Print Sh
Sheets(Sh).Activate
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
Next Sh
Application.ScreenUpdating = True

End Sub

 

[Reza1001]

it works . thank u
another question . how can i Refer to sheets by CodeName instead sheet names in this macro? means for this part : For Each Sh In Array("Sheet2", "Sheet5", "Sheet6", "Sheet9")

 

[kvsrinivasamurthy]

You can use the actual sheet names.

 

[frabulator]

it works . thank u
another question . how can i Refer to sheets by CodeName instead sheet names in this macro? means for this part : For Each Sh In Array("Sheet2", "Sheet5", "Sheet6", "Sheet9")

by 'CodeName' do you mean the programable sheet number, so the code doesnt break when the sheet is renamed?

You can do this by just referring to the number of the sheet itself. Typically this is 1 for Sheet1, 2 for Sheet2 and so on.

It would look like this.

Sheets(1).Activate

 

[MARK858]

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

 

[Reza1001]

You can use the actual sheet names.

my sheet names are changing monthly . that's why i am trying to use codenames which are permanent . even by adding or removing sheets . Array("Sheet2", "Sheet5", "Sheet6", "Sheet9") is reffering to sheet names not codenames of sheets . for example codnames of my sheets are these : "Sheet2"=sheet1 "Sheet5"=sheet3 "Sheet6"=sheet2 "Sheet9"=sheet5

 

[Reza1001]

by 'CodeName' do you mean the programable sheet number, so the code doesnt break when the sheet is renamed?

You can do this by just referring to the number of the sheet itself. Typically this is 1 for Sheet1, 2 for Sheet2 and so on.

It would look like this.

Sheets(1).Activate

thanks . you are referring to index names. Codenames are better without the problems of renaming and reordering sheets

 

[dmt32]

how can i Refer to sheets by CodeName instead sheet names in this macro? means for this part : For Each Sh In Array("Sheet2", "Sheet5", "Sheet6", "Sheet9")

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

 

[MARK858]

Hi
untested but try this update to your code

Seems to work for me