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.

[VBA] 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
[/VBA]

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.

[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 macro[VBA]Range("A2", Range("F31").End(xlUp).Address).Select[/VBA] can anybody help with fixing or modifying this macro or give me some advice on how to get it to work.

[VBA] 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
[/VBA]

[rbrhodes]

cross post

http://www.ozgrid.com/forum/showthre...944#post232944

[Bob Phillips]

Try this

[vba]
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
[/vba]

[Barryj]

Am getting a 1004 error [VBA] .Sort key1:=Range("F2"), _
Order1:=xlAscending, _
Key2:=Range("E2"), _
Order2:=xlAscending, _
Key3:=Range("D2"), _
Order3:=xlAscending[/VBA]
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.