-
Call Macros in other worksheets or selection change event?
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.
-
Why not use activate to populate and sort?
-
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.
-
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]
-
-
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]
-
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.
-
-
Thankyou very much this worked fantastic.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules