Trigger Macro On Another Sheet

Re: Trigger Macro On Another Sheet

Put it in a Standard Module and make sure the ranges that it references include the worksheet. Then call the m,acro.

Re: Trigger Macro On Another Sheet

Hi,

Since it is an Activate sub you're calling you could simply activate the sheet and then return to your calling sheet. You'd need <CTRL+Break> to ever see the sheet again, tho!

Current sheet:

<snip>
    End With
 
'Add
    Sheets("YourSheetNameToSort").activate


    Application.ScreenUpdating = True 
     
End Sub

Activated Sheet:

<snip>     
    
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _ 
    , Key2:=Range("C2"), Order2:=xlAscending _ 


'Add
    Sheets("SheetThatActivatedMeName").activate
     
End Sub

Really clunky.

Better to change your Sheet_Activate Sub to a normal routine and 'Call' it:

Current sheet:

<snip>
    End With
 
'Add
     Call SortMe


    Application.ScreenUpdating = True 
     
End Sub

Sub SortMe


...your sort code


End Sub

or just put the code in the Calc Sub?

PS: You probaly need to unhide/hide the sheet as well

Application.ScreenUpdating = False
    Sheets(YourSheet).Visible = True
   
   '...your code...
    
    Sheets(YourSheet).Visible = False
    Application.ScreenUpdating = True

Cheers,

dr

Re: Trigger Macro On Another Sheet

Hi,

Rather than repost your sheet here's the code. The sort sheet was named wrong in the Sub. I added an activate event to fire the second sheet code.

[vba]
Private Sub Worksheet_Calculate()

Dim r As Range, grade, c As Range
Dim i As Integer, sn, x As Range
Dim rng As Range
'Set the range as Dynamic
Set rng = Range([B19], [V65536].End(xlUp))

grade = Array("A")

'Changed to correct name
sn = Array("Stableford Score Sheet")

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("Stableford")
'? Start at A19"
For Each r In .Range("a19", .Range("a65536").End(xlUp))
If r.Offset(0, 1).Value = "" Then GoTo SkipIt1
'? Goes to 'next i' - should go to next r?
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(, 22).Resize(, 1).Value
x.Offset(, 2).Value = r.Offset(, 23).Value
x.Offset(, 3).Value = r.Offset(, 21).Value
x.Offset(, 4).Value = r.Offset(, 20).Value
x.Offset(, 5).Value = r.Offset(, 19).Value
Exit For
End If
SkipIt1:
Next i
'End If (implied)
Next r
End With

' Quote: "I want to get it to run each time the first macro runs"

'dr reply: Since it is an 'Activate' event, activate the sheet so the sub runs:

Sheets("Stableford Score Sheet").Activate

' then return to this sheet:

Sheets("Stableford").Activate

Application.ScreenUpdating = True

'Options (instead of the 'Activate')

'...put the sort code in here...

'...name the sub and call it from here...

End Sub
[/vba]

Cheers,

dr

Re: Trigger Macro On Another Sheet

First off it's a select statement. IF the worksheet in question is not the active worksheet then it's going to error.

As I understand this routine it's supposed to perform sort ranges on multiple sheets.

I'll try to make you a couple of routines which will accomplish what it appears that you want.

Re: Trigger Macro On Another Sheet

Cross post

Re: Trigger Macro On Another Sheet

Now these routines have not been tested. As your sample workbook did not have the indicated sheets with any data to use.

So here is the worksheet activate routine

Private Sub Worksheet_Activate()




'   When worksheet is activated sort the following sheets
    For intCount = 1 To 4
        Select Case intCount
            Case 1: strSheetName = "A Grade Rdl"
            Case 2: strSheetName = "A Grade Nett Rd"
            Case 3: strSheetName = "B Grade Nett Rd1"
            Case 4: strSheetName = "B Grade Rd1"
        End Select
        With Sheets(strSheetName)
            Call SortWorksheet_Range(.Range("A2", .Range("F31").End(xlUp)), .Range("F2"), xlAscending, .Range("E2"), xlAscending, .Range("D2"), xlAscending, xlTopToBottom)
            Call SortWorksheet_Range(.Range("A2", .Range("F31").End(xlUp)), .Range("B2"), xlAscending, .Range("C2"), xlAscending, varOrientation:=xlTopToBottom)
        End With
    Next intCount
    
    
End Sub

And here is the sorting routine.

Sub SortWorksheet_Range(rngTgt As Range, _
                        rngKey1 As Range, varOrder1 As Variant, _
               Optional rngKey2 As Range, Optional varOrder2 As Variant, _
               Optional rngKey3 As Range, Optional varOrder3 As Variant, _
               Optional varOrientation As Variant)


'   Set Default Orientation
    If IsMissing(varOrientation) Then varOrientation = xlTopToBottom


'   Sort 3 Keys
    If rngKey1 Is Nothing And rngKey2 Is Nothing And rngKey3 Is Nothing Then
        Range(rngTgt).Sort Key1:=rngKey1, Order1:=varOrder1, Key2:=rngKey2, Order2:=varOrder2, Key3:=rngKey3, Order3:=varOrder3, Orientation:=varOrientation
    End If


'   Sort 2 Keys
    If rngKey1 Is Nothing And rngKey2 Is Nothing And Not rngKey3 Is Nothing Then
        Range(rngTgt).Sort Key1:=rngKey1, Order1:=varOrder1, Key2:=rngKey2, Order2:=varOrder2, Orientation:=varOrientation
    End If


'   Sort 1 Key
    If Not rngKey1 Is Nothing And rngKey2 Is Nothing And rngKey3 Is Nothing Then
        rngTgt.Sort Key1:=rngKey1, Order1:=varOrder1, Orientation:=varOrientation
    End If


End Sub