Consulting

Results 1 to 9 of 9

Thread: Call Macros in other worksheets or selection change event?

  1. #1
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    248
    Location

    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.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,418
    Location
    Why not use activate to populate and sort?

  3. #3
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    248
    Location
    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.

  4. #4
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    248
    Location
    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]

  5. #5
    VBAX Expert
    Joined
    Feb 2005
    Location
    Nanaimo, British Columbia, Cananda
    Posts
    568
    Location

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,418
    Location
    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]

  7. #7
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    248
    Location
    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.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,418
    Location
    .

  9. #9
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    248
    Location
    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
  •