Having a master commandbutton "click" another button on a different worksheet

Hello All

Running into this issue of "transworksheet macros"

I have a master "update" commandbutton on sheet 1. I have two sub "update" buttons on sheet 2 and 3 respectively.

What I would like to do is have the user click the master button, that would "click" the two sub buttons so they run their respective macros on their respective worksheets (within the same worksheet.

I have tried pasting the macro code in the master button, telling it to select the sub worksheet and then run the macro, but the marco runs itself on the master worksheet instead.

I tried to use the .goto function, but I don't think I know the proper syntax to make that work.

I have posted the code below with the line I think I need help with in bold.

Private Sub CommandButton2_Click()
' populate Monthly Clinician Summary Macro
 Application.Goto Sheets("MonthlyClinicianSummary").Range("C11:C5000")
    Range("C11:C5000").Copy Destination:=Range("A55400")
    Range("A55398:A60398").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Range("A55399:A56296").Copy Destination:=Range("A55299")
    Application.CutCopyMode = False
    With Range("A55300:A55397").Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Interior.ColorIndex = xlNone
    Range("A55302").Select

The first thing you need to do is remove "Private" from buttons 2 and 3. Then, to call them, you can use:

Call Sheet2.CommandButton2_Click
Call Sheet3.CommandButton3_Click

Be sure to use the correct sheet and command button names.

HTH

Jason

Originally Posted by shg

It would be better practice for the button code to call a routine in a code module to do the processing. That routine could be called by any other.

I do I make something a routine?

And also, in the master button, how do I specify which worksheet ("sub" worksheet) that I want the routine to run on?

took me a while to find the module screen. but it works beautifully.

Thank you so much.

According to VBA editor the sub button is on sheet 2, commandbutton2,
so I used that

Excel gives following prompt

Run-time Error 1004
Application-defined orobject-defined error

So do I use the name I've labeled the sheet?

If so, what is the syntax?

Call Sheets("MySheet").CommandButton2_Click

If so, then I get the

Select method of range class failed

error.

Please advise