Barryj
01-19-2006, 05:54 PM
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
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