How do I write a macro that would "click" a command button i.e. another macro

[lilbusgirl]

Hi I am trying to write a macro that would "click" a command button that is in another tab contained within the workbook. Would that be possible? Without changing any of the code within the other tabs?

I don't have access to the other macro as it was developed by another software person.

I have 6 workbooks with approx 60tabs in each one and I didn't want to go into each tab and press the command button 'calculate' each time. I wanted to streamline it with having one macro which I named 'compute all' to do it for me.

The code that's been written only recognises the cell (H38) behind the calculate button, so you get the picture, the sheets ("0.5RC 0.5LC") are one of the 60 tabs within the workbook.

Sub Macro5()
'
' Macro5 Macro
' Run all scenarios at once
'
' Keyboard Shortcut: Ctrl+q

Sheets("0.5RC 0.5LG").Select
Range("H38").Select
Sheets("0.5RC 1.5LG").Select
Range("H38").Select
Sheets("0.5RC 2LG").Select
Range("H38").Select
Sheets("0.5RC 2.5LG").Select
Range("H38").Select
Sheets("0.5RC 3.5LG").Select
Range("H38").Select
Sheets("0.5RC 4LG").Select
Range("H38").Select

I have looked into using below but this doesn't work as I don't want to have to change the name of the excel spreadsheet each time. Sub Run_Macro()

Workbooks.Open Filename:="C:\Book1.xls"
'Open the workbook containing the command button
'Change the path and filename as required

Application.Run "Book1.xls!Macro1"
'Run the macro
'Change the filename and macro name as required

'If the macro is attached to a worksheet rather than a module, the code would be
'Application.Run "Book1.xls!Sheet1.Macro1"

Help been googling solutions but nothing is remotely close to what I want to do.​

 

[Michael M]

Hi there
If you Press ALT + F8, you will see a list of macros.
If you know which macro is the calculate macro you could call it from your own code.
Something like this...assuming the macro is called Macro1

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    Call Macro1
Next ws
End Sub

 

[lilbusgirl]

Hey Michael
I tried to use that

Sub Macro5()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Call OnGradeCombinationInlets
Next ws
End Sub

But I get a compile error: sub or function not defined.

Pressing Alt F8 the macro name is OngradeOnGradeCombinationInlets

Any ideas on what else to try?

 

[alansidman]

Pressing Alt F8 the macro name is OngradeOnGradeCombinationInlets

Here you have the call with a different name.

Call OnGradeCombinationInlets

Is that a typo or did you misspell the macro

 

[lilbusgirl]

Yep sorry that was a typo. I did try Call OnGradeCombinationInlets and for an error message

Here you have the call with a different name.

Call OnGradeCombinationInlets

Is that a typo or did you misspell the macro

 

[Michael M]

Is Macro5 in the same workbook as "OnGradeCombinationInlets" ??
If not, it needs to be !

Otherwise, if you can't access the other code, I'd suggest working out what the macro does and rewriting it.

 

[lilbusgirl]

Yes its in the same workbook. I was afraid you were going to suggest that. That macro OnGradeCombinationInlets was written by someone else which I don't have access to. Any suggestions on being able to view the code to copy it into my macro?

 

[Michael M]

There are methods available on the Internet, but as per Forum rules were are not allowed assist, recommend or advise how or where to do this.