Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Multiple Worksheets Simultaneously

    I've looked everywhere including this forum trying to find an answer as to whether there is a way to sort multiple worksheets simultaneously. When I group the worksheets the filter button at the top of a column does not function. Any help would be appreciated.

    Kent

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As far as I know, sorting must be done on contiguous ranges. Ranges on separate worksheets are not contiguous.

    Steve

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did find some VBA code that works as long as I go in and change the column I want to sort on each time or I guess I could have a macro for each column I might want to sort on. Better than nothing. Here is the code. If anyone has a better idea I would love to hear.

    Sub SortAllSheets()
    'this sorts each sheet in same fashion
    'assumes row 1 has labels
    '
    'redefine these to suit your requirements
    Const firstColToSort = "A"
    Const lastColToSort = "C"
    Const keyCol = "A" ' field to sort on
    'this next should be a column that will
    'always have entries in it, and can be
    'same as keyCol but does not have to be.
    Const testCol = "C"

    Dim anyWS As Worksheet
    Dim sortRange As Range
    Dim sortKey As Range
    'next improves performance
    Application.ScreenUpdating = False
    For Each anyWS In ThisWorkbook.Worksheets
    Set sortRange = anyWS.Range(firstColToSort & "1:" _
    & lastColToSort & _
    anyWS.Range(testCol & Rows.Count).End(xlUp).Row)
    Set sortKey = anyWS.Range(keyCol & 2)
    sortRange.Sort Key1:=sortKey, Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Next
    'just good housekeeping
    Set sortRange = Nothing
    Set sortKey = Nothing
    Set anyWS = Nothing
    End Sub

  4. #4
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Steve. I did find something that I just posted.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This won't do what you asked. It will not sort simultaneously. It is equivalent to sorting each sheet separately and apart from each other sheet. Your request seemed to suggest you wanted them sorted/combined in some way...

    Steve

  6. #6
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are correct and I started figuring it out as I worked with it more. So what you are saying from your first comment is that there is nothing will work?

    Kent

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps if you explained what you want to really accomplish. I don't understand what you mean by sorting multiple sheets simultaneously. What do you start with and what do you want to end with?

    Steve

  8. #8
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Lets say I have 10 worksheets in the workbook, each one for a different customer, and they all have the same columns with same data except for a column that has sales data. I originally applied the sort/filter to each column on every worksheet then grouped the worksheets and tried to do a sort on one of the columns so that all the worksheets would sort the same way at the same time but the sort/filter would not let you do that with the worksheets being grouped. I think the code I posted comes pretty close to doing what I want without having to group the worksheets. I just have to create a module for each column that I might want to sort on then whenever I want to sort all the worksheets using a certain column I go to macros and run the column that I want. Hope that makes sense.

    Thanks,

    Kent

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That code should do that, just define the constants based on the range of the data, and the column to sort by. It will go through each sheet, one at a time, sorting each one by the defined column.

    [As an aside: I would combine all your worksheets into one, adding a column for customer name. You could then use the autofilter to filter on the customer name and essentially have the output you now have in "10 worksheets". This grouped dataset can use other builtin features (like pivot table) to summarize all the data and look at all the business instead of having to look at and treay each customer as a separate entity (separate filters, separate summaries, etc). In my experience (for what it is worth) it is not of value to have multiple identically formatted worksheets, with similar data. It is much better to make one database of all the data.]

    Steve

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2011-06-30)

  11. #10
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Steve.

    I agree, under normal circumstances I would do what you suggested by consolidating all the spreadsheets into one but for this particular application I needed to keep things separate. Would probably take too much space to describe why.

    Again thanks for your help.

    Kent

Posting Permissions

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