Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can a Custom Sort created in an Excel worksheet be used in another worksheet?

    I am new to the forum and here is first question: I have an Excel workbook having one worksheet (Sheet 1) containing 10 columns and 50 rows. I have sorted Sheet 1 using a Custom Sort, sorting 1st by Column A, then by Column B and then by Column C. I now wish to add a new worksheet (Sheet 2) to the workbook, and would like to know the following:

    1. If Sheet 2 has the same number of Columns and Rows as Sheet 1, is there a way to copy the Custom Sort from Sheet 1 and paste it (apply it?) to Sheet 2? If so, how?

    2. If the answer to question 1. is yes, then is there a way to copy the Custom Sort from Sheet 1 and paste it (apply it?) to a worksheet in some other workbook? If so, how?

    Thanks.
    mburke

  2. #2
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,726
    Thanks
    147
    Thanked 156 Times in 149 Posts
    For sorting, best to click on help and enter sort - there's a few topics there at least one of which talks about importing custom lists. That might help you.

  3. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by access-mdb View Post
    For sorting, best to click on help and enter sort - there's a few topics there at least one of which talks about importing custom lists. That might help you.
    Thank you for your suggestion - which I tried but was unable to find any answer to my question. If anyone else has some thoughts, I would be happy to hear same.

    Thank you.
    mburke

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    mburke,

    You could place the following code in a standard module and assign it a shortcut (ex. CTRL-z). If you visit a different sheet, pressing CTRL-z will sort the current sheet with the criteria you stated (assuming you have a header row followed by 50 rows of data).

    Code:
    Sub CustomSort()
    Dim Rng As Range
    Set Rng = Range("A1:A51")
    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Rng
        .Sort.SortFields.Add Key:=Rng.Offset(0, 1)
        .Sort.SortFields.Add Key:=Rng.Offset(0, 2)
        With .Sort
            .SetRange Range("A1:J51")
            .Header = xlYes
            .Apply
        End With
    End With
    End Sub

  5. #5
    New Lounger
    Join Date
    Sep 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maudibe -

    Thank you for sending what looks like the answer to my question - and I can only say "looks like" as I haven't yet tried learning what to do with the code you provided (as I am just a code newbie - but plan on getting stronger, faster, etc.). And please don't take my delay in sending this thanks (or my trying to learn how to use the code) as a lack of appreciation; on the contrary, I very much appreciate your help - and may be asking for more. Stay tuned.

    mburke

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    mburke,

    In Excel, press Alt-F11 and the VB editor will open. In the VB Editor, click Insert on the toolbar then Module. A blank large white standard module window will open. Paste the code in that white area then close or minimize the VB editor. Back in Excel, Developer Tab > Macros > Click on CustomSort in the macro list window so that it appears in the macro name box above it. While CustomSort is highlighted, click options > press z to enter z as the shortcut > OK > "X" out.

    Now visit one of your sheets that you want formatted and press CTRL-z.

    HTH,
    Maud

  7. #7
    New Lounger
    Join Date
    Sep 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud,

    Thank you again for the step-by-step how-to. I played with it a little bit, but didn't devote sufficient time needed for all to sink in - and will revisit. But wanted to send thanks in the meantime.

    mburke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •