VBA code to activate unique macros within seperate Excel worsheets

[MissLady11]

I have "active sheet" VBA code that updates fields within each seperate worksheet.
For each of those worksheets, I've created a button (on each worksheet) that is used to run the code/macro for that particular sheet
I want to create a macro on a master sheet that will go to each individual sheet and run
each named macro or push each button on individual sheets.

macos are as follows:
Sheet27.TQCupdate (assignd to button 1)
Sheet28.EBIupdate (assgned to button 2)
Sheet29.BSSupdate (assgned to button 3) and so on for 13 different macros

I tried to use the following in a module but it does not work from my master tab

Sub RunUpdates()
Application.Run "Sheet27.TQCupdate"
Application.Run "Sheet28.EBIupdate"
Application.Run "Sheet29.BSSupdate"
End Sub

I'm not a coder sorry if this sounds simple, any help wold be appreciated

 

[Gangadhar Yeligaty]

Hi,

Please try below code

Sub RunUpdates()

Call Sheet27.TQCupdate
Call Sheet28.EBIupdate
Call Sheet29.BSSupdate

End Sub

 

[kchiba]

Hi,

Delete the Application.Run from each line and it should work. You can also replace Application.Run with Call

kanti

 

[MissLady11]

Gangadhar/Kanti

I tried both ways and I can't seem to get it to work....I get
"Run-time error 5:Invalid procedure call or argument"

It may be the way I set the code up on the individual worksheets...see code below for the code example that I use on the individual worksheets.
I have also uploaded the file...
I have the below code in the individual "...Slide" worksheets.
I am trying to assign a macro to the Master worksheet (to the "Update Scorecard Tabs" button) that will go to each one of the ...slide worksheets and either push the update button that exist or run each macro that is assigned to each button.
I am currently trying this in my module 4 on the attached workbook


Hope this is more helpful...

Sub TQCUpdate()
    Application.ScreenUpdating = False
'Missing Cost Categories'
    If Range("G41") <= 0.1 Then
        ActiveSheet.Shapes("Cube 97").Fill.ForeColor.RGB = RGB(131, 163, 67)
   
    ElseIf Range("G41") > 0.1 And Range("G41") < 0.21 Then
        ActiveSheet.Shapes("Cube 97").Fill.ForeColor.RGB = RGB(252, 246, 0)
       
    Else
        ActiveSheet.Shapes("Cube 97").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
   
'Missing Categories'
  If Range("H41") <= 0.1 Then
        ActiveSheet.Shapes("Cube 98").Fill.ForeColor.RGB = RGB(131, 163, 67)
   
    ElseIf Range("H41") > 0.1 And Range("H41") < 0.21 Then
        ActiveSheet.Shapes("Cube 98").Fill.ForeColor.RGB = RGB(252, 246, 0)
       
    Else
        ActiveSheet.Shapes("Cube 98").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
   
'Utilized < %80'
    If Range("I41") <= 0.25 Then
        ActiveSheet.Shapes("Cube 99").Fill.ForeColor.RGB = RGB(131, 163, 67)
   
    ElseIf Range("I41") > 0.25 And Range("I41") < 0.36 Then
        ActiveSheet.Shapes("Cube 99").Fill.ForeColor.RGB = RGB(252, 246, 0)
       
    Else
        ActiveSheet.Shapes("Cube 99").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
   
'Allocated > %120'
        If Range("J41") <= 0.1 Then
        ActiveSheet.Shapes("Cube 100").Fill.ForeColor.RGB = RGB(131, 163, 67)
   
    ElseIf Range("J41") > 0.1 And Range("J41") < 0.21 Then
        ActiveSheet.Shapes("Cube 100").Fill.ForeColor.RGB = RGB(252, 246, 0)
       
    Else
        ActiveSheet.Shapes("Cube 100").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
   
      'Missing Roles'
  If Range("K41") <= 0.1 Then
        ActiveSheet.Shapes("Cube 101").Fill.ForeColor.RGB = RGB(131, 163, 67)
   
    ElseIf Range("K41") > 0.1 And Range("K41") < 0.21 Then
        ActiveSheet.Shapes("Cube 101").Fill.ForeColor.RGB = RGB(252, 246, 0)
       
    Else
        ActiveSheet.Shapes("Cube 101").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
   
    'Const > %140'
  If Range("L41") <= 0.25 Then
        ActiveSheet.Shapes("Cube 102").Fill.ForeColor.RGB = RGB(131, 163, 67)
   
    ElseIf Range("L41") > 0.25 And Range("L41") < 0.36 Then
        ActiveSheet.Shapes("Cube 102").Fill.ForeColor.RGB = RGB(252, 246, 0)
       
    Else
        ActiveSheet.Shapes("Cube 102").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
   
    '> 5 Days'
  If Range("M41") >= 1 Then
        ActiveSheet.Shapes("Cube 103").Fill.ForeColor.RGB = RGB(131, 163, 67)
       
    Else
        ActiveSheet.Shapes("Cube 103").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
   
'more than 1 pool w 3 RMs'
    If Range("N41") >= 1 Then
        ActiveSheet.Shapes("Cube 104").Fill.ForeColor.RGB = RGB(131, 163, 67)
       
    Else
        ActiveSheet.Shapes("Cube 104").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If

    'Overal Score'
  If Range("G42") <= 2 Then
        ActiveSheet.Shapes("Oval 53").Fill.ForeColor.RGB = RGB(131, 163, 67)
   
    ElseIf Range("G42") > 2 And Range("G42") < 6 Then
        ActiveSheet.Shapes("Oval 53").Fill.ForeColor.RGB = RGB(252, 246, 0)
       
    Else
        ActiveSheet.Shapes("Oval 53").Fill.ForeColor.RGB = RGB(176, 65, 62)
    End If
    Application.ScreenUpdating = True
End Sub

 

[KenU]

@MissLady11,

I believe the error can be avoided by activating each sheet in turn before running the corresponding routine to update that sheet (see below).

Sub RunUpdates()

Sheet27.Activate
Call Sheet27.TQCUpdate

Sheet20.Activate
Call Sheet20.EBIUpdate

Sheet29.Activate
Call Sheet29.BSSUpdate

End Sub

As I was looking at the slide sheets, I also noticed that some labels (textbox formulas) seem to be referencing the wrong data sheets.

Hope that helps.

Regards,
Ken

 

[MissLady11]

@MissLady11,

I believe the error can be avoided by activating each sheet in turn before running the corresponding routine to update that sheet (see below).

Sub RunUpdates()

Sheet27.Activate
Call Sheet27.TQCUpdate

Sheet20.Activate
Call Sheet20.EBIUpdate

Sheet29.Activate
Call Sheet29.BSSUpdate

End Sub

As I was looking at the slide sheets, I also noticed that some labels (textbox formulas) seem to be referencing the wrong data sheets.

Hope that helps.

Regards,
Ken

Thanks Ken!
This code works and thanks for the heads up on the reference!!

Thanks all for your assistance