Calling routines from different sheets

[Peter]

I have a muti-sheet workbook with many macros on each sheet (i.e. not in
modules) and in userforms.
I need to be able to run a macro on one sheet then makes another sheet
active and then calls a macro stored in that sheet.

So Sheet1 has a number of buttons on it. One of them says "sort" and the
user presses this and the code behind the button runs. On Sheet2 there
are more command buttons and one of these says "check data". The plan is
that this button does some work then makes sheet1 active and then runs the
code under the commandbutton called "Sort". How do I make that happen.

Finally can you explain what is the difference/advantage of using Modules
to hold the code over having it in the sheets behind command buttons.

many thanks

Peter

 

[JLGWhiz]

Without seeing the code behind the buttons, it would be difficult to give
advice on how to use the code from another sheet, because the code would
most likely run based on the sheet that contained the code. If you intend
to use code in more than one sheet or for more than one UserForm, you can
put it in the public module and then use the controls (buttons, forms, etc.)
to call the various procedures. For example:

In Sheet1 module:

Private Sub CommandButton1_Click()
Macro1
End Sub

In Sheet2 module:

Private Sub CommandButton1_Click()
Macro1
End Sub

In the public module:

Sub Macro1()
ActiveSheet.UsedRange.Sort Key1:=Range("A1")
End Sub

You can use the same macro by clicking either button if you want to sort
your used range on a sheet.