Call Macros in other worksheets or selection change event?

[Barryj]

I have 4 sheets that are used for sorting data, this data is placed there from a Macro on another sheet, once the data is placed in the sorting sheet, it is activated by opening the sheet, the data is then linked to a results page.

I want to either get the macro to sort with a selection change event or call the macro's from the results sheet when it is opened.

Is it possible to have a selection change function activate a macro when the data is put into a sheet by a macro as I have tried this method without success.

This is the Macro that I am currently using to sort the 4 sheets.

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


At the moment I have no code on the results sheet, what I need is some code to either call the macros or a selection change modification to this code.

Thanks for any help.:dunno

[Bob Phillips]

Why not use activate to populate and sort?

[Barryj]

What I want to do is then hide these sheets so that I only have 2 sheets displayed in the tabs, so that when other people use this workbook they dont forget to sort before going to the results sheet.

[Barryj]

Ok, I got this macro together to try and sort the four sheets that I require but am getting a 1004 runtime error on the third row in first part of macroRange("A2", Range("F31").End(xlUp).Address).Select can anybody help with fixing or modifying this macro or give me some advice on how to get it to work.

Private Sub Worksheet_Activate()

With Sheets("A Grade Rd1").Select
Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _


Sheets("A Grade Nett Rd").Select
Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _

Sheets("B Grade Rd1").Select
Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _
Sheets("B Grade Nett Rd1").Select
Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _
End With
End Sub

[rbrhodes]

cross post

http://www.ozgrid.com/forum/showthread.php?p=232944#post232944

[Bob Phillips]

Try this


Private Sub Worksheet_Activate()

With Sheets("A Grade Rd1")
With .Range("A2", .Range("F31").End(xlUp))
.Sort key1:=Range("F2"), _
Order1:=xlAscending, _
Key2:=Range("E2"), _
Order2:=xlAscending, _
Key3:=Range("D2"), _
Order3:=xlAscending
.Sort key1:=Range("B2"), _
Order1:=xlAscending, _
Key2:=Range("C2"), _
Order2:=xlAscending
End With
End With

With Sheets("A Grade Nett Rd")
With .Range("A2", .Range("F31").End(xlUp))
.Sort key1:=Range("F2"), _
Order1:=xlAscending, _
Key2:=Range("E2"), _
Order2:=xlAscending, _
Key3:=Range("D2"), _
Order3:=xlAscending
.Sort key1:=Range("B2"), _
Order1:=xlAscending, _
Key2:=Range("C2"), _
Order2:=xlAscending
End With
End With

With Sheets("B Grade Rd1").Select
With .Range("A2", .Range("F31").End(xlUp))
.Sort key1:=Range("F2"), _
Order1:=xlAscending, _
Key2:=Range("E2"), _
Order2:=xlAscending, _
Key3:=Range("D2"), _
Order3:=xlAscending
.Sort key1:=Range("B2"), _
Order1:=xlAscending, _
Key2:=Range("C2"), _
Order2:=xlAscending
End With
End With

With Sheets("B Grade Nett Rd1")
With .Range("A2", .Range("F31").End(xlUp))
.Sort key1:=Range("F2"), _
Order1:=xlAscending, _
Key2:=Range("E2"), _
Order2:=xlAscending, _
Key3:=Range("D2"), _
Order3:=xlAscending
.Sort key1:=Range("B2"), _
Order1:=xlAscending, _
Key2:=Range("C2"), _
Order2:=xlAscending
End With
End With

End Sub

[Barryj]

Am getting a 1004 error .Sort key1:=Range("F2"), _
Order1:=xlAscending, _
Key2:=Range("E2"), _
Order2:=xlAscending, _
Key3:=Range("D2"), _
Order3:=xlAscending
On the first sort.

Thanks for the assistance.
Have included a file of the sheets required to be sorted, the results sheet is blank as it is not linked yet, as long as the sort works.

[Bob Phillips]

.

[Barryj]

Thankyou very much this worked fantastic.