Help with running macro on another sheet

[Barryj]

I am running the first macro on one sheet and would like it to run or trigger a macro on another sheet, the second macro is sheet activated at the moment but I want to hide this sheet as the macro only sorts data and the sorted data is then linked to another sheet?

Macro 1

Private Sub Worksheet_Calculate()
Dim rng As Range
' Set the range as Dynamic
Set rng = Range([B13], [V65536].End(xlUp))
Dim r As Range, grade, c As Range
Dim i As Integer, sn, x As Range
grade = Array("A", "B")
sn = Array("A Grade Rd1", "B Grade Rd1")
Application.ScreenUpdating = False
For i = LBound(sn) To UBound(sn)
Sheets(sn(i)).Cells.Resize(Cells.Rows.Count - 1).Offset(1).ClearContents
Next
With Sheets("stroke Rd1")
For Each r In .Range("a11", .Range("a65536").End(xlUp))
If r.Offset(0, 1).Value = "" Then GoTo SkipIt1
For i = LBound(grade) To UBound(grade)
If r.Value = grade(i) Then
Set x = Sheets(sn(i)).Range("a65536").End(xlUp).Offset(1)
x.Value = r.Offset(, 1).Value
x.Offset(, 1).Resize(, 2).Value = r.Offset(, 23).Resize(, 1).Value
x.Offset(, 2).Value = r.Offset(, 24).Value
x.Offset(, 3).Value = r.Offset(, 22).Value
x.Offset(, 4).Value = r.Offset(, 21).Value
x.Offset(, 5).Value = r.Offset(, 20).Value
Exit For
End If
SkipIt1:
Next
Next
End With
Application.ScreenUpdating = True
grade = Array("A", "B")
sn = Array("A Grade Nett Rd1", "B Grade Nett Rd1")
Application.ScreenUpdating = False
For i = LBound(sn) To UBound(sn)
Sheets(sn(i)).Cells.Resize(Cells.Rows.Count - 1).Offset(1).ClearContents
Next
With Sheets("stroke Rd1")
For Each r In .Range("a11", .Range("a65536").End(xlUp))
For i = LBound(grade) To UBound(grade)
If r.Offset(0, 1).Value = "" Then GoTo SkipIt2
If r.Value = grade(i) Then
Set x = Sheets(sn(i)).Range("a65536").End(xlUp).Offset(1)
x.Value = r.Offset(, 1).Value
x.Offset(, 1).Resize(, 2).Value = r.Offset(, 25).Resize(, 1).Value
x.Offset(, 2).Value = r.Offset(, 24).Value
x.Offset(, 3).Value = r.Offset(, 22).Value
x.Offset(, 4).Value = r.Offset(, 21).Value
x.Offset(, 5).Value = r.Offset(, 20).Value
Exit For
End If
SkipIt2:
Next
Next
End With
Application.ScreenUpdating = True
End Sub
[VBA]

Macro 2

[VBA] Private Sub Worksheet_Activate()
Range("A2", Range("F65536").End(xlUp).Address).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending _
, Key2:=Range("F2"), Order2:=xlAscending _
End Sub


I need the macro to run after each time that the first macro is triggered,
do I need to rename the second macro, and how would I do this.

Thanks for any help on this.:help

[mdmackillop]

Hi Barry,
Put your main code in a standard module under another name (MainRoutine or whatever), and call it from the Worksheet sub.

eg
Private Sub Worksheet_Calculate() or WorkSheet_Activate()
Call MainRoutine
End Sub

[Barryj]

Not having any luck, can you step me through the process as I have never done this type of thing before!

Thanks for the help.

[XLGibbs]

barryj:

The area you have that code is called the Sheet module as it is located in the VBProject for your file within the Sheet code. You can see the project on the left of the VB Editor. Right click on the Project title and select Insert>Module..

double click that newly created module. Copy the code from your First macro into the routine by change the words after: Private Sub Worksheet_Calculate() to :

Private Sub MainCode()


then in your Worksheet_Calculate (where the code should be removed from as above) place your SECOND code but put the Call MainCode line above itlike so
Private Sub Worksheet_Activate()
Call MainCode

Range("A2", Range("F65536").End(xlUp).Address).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending _
, Key2:=Range("F2"), Order2:=xlAscending _
End Sub

[Barryj]

Still have be unable to get this to work, I have included a copy of the workbook which includes the macros on each sheet.

Would someone be kind enough to have a look and show how to alter the macro's and where to place them to make it auto run.

Thanks again.

[mdmackillop]

Hi,
Try the attached. Your initial macro is still triggered by a calculation event
Regards
MD

[Barryj]

Sorry, but I cannot find the sort code on the attached file?

[mdmackillop]

The sort code is in Module 1. Becuase you are triggering your code with a Calculation event, you need to do one for the code top run. Anywhere on sheet Stableford, enter =3.