+ Reply to Thread
Results 1 to 12 of 12

Sorting Tabs in workbook using a list

  1. #1
    Registered User
    Join Date
    06-26-2012
    Location
    Burlington
    MS-Off Ver
    Excel 2010
    Posts
    6

    Sorting Tabs in workbook using a list

    Hi,

    I have an issue...i have a workbook with 124 tabs that are not currently in any specific order. They have not been named consistently(sorting does not work!) and it would take me a really long time to try and sort them so that they are in the order that i need. If i could write some code that would accept a list(order that i need) and then sort the tabs exactly how the list is defined, then I would be a very happy camper. The problem being, I have no idea how to do this.

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Sorting Tabs in workbook using a list

    To sort the worksheets alphabetically

    Please Login or Register  to view this content.
    Click on star (*) below if this helps

  3. #3
    Registered User
    Join Date
    06-26-2012
    Location
    Burlington
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sorting Tabs in workbook using a list

    Thanks for the reply but I will be grouping them, not sorting them. I guess i didnt specify that. In other words i have 124 sheets, 31(# of days in august)*4 groups(recon, invoice detail, payment detail, adjustments).

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Sorting Tabs in workbook using a list

    Try this link for other ways to sort the worksheets, ie by color, etc.
    http://www.cpearson.com/excel/sortws.aspx

  5. #5
    Registered User
    Join Date
    06-26-2012
    Location
    Burlington
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sorting Tabs in workbook using a list

    Although that was the correct solution, I could not get it to work.

    Lets just say you have a workbook with 10 tabs:

    tab 1 = invoice1
    tab 2 = invoice3
    tab 3 = invoice4
    tab 4 = payments1
    tab 5 = payments2
    tab 6 = payments4
    tab 7 = recon1
    tab 8 = recon2
    tab 9 = recon3
    tab 10 = recon4

    If you did a numerical sort you would get the same order...but i want the following order:

    tab 1 = payments1
    tab 2 = invoice1
    tab 3 = recon1
    tab 4 = payments2
    tab 5 = recon2
    tab 6 = invoice3
    tab 7 = recon3
    tab 8 = payments4
    tab 9 = invoice4
    tab 10 = recon4

    Please note that the order of the list is not alphabetical at all!!! The only thing unique about the grouping is the order of the numbers, other than that you could consider this a user defined grouping that could only be handled by either manually changing the orderof the tabs or by inputting a list that defines the order of the tabs.

    Please help!

  6. #6
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Sorting Tabs in workbook using a list

    How about sorting by tab color?

  7. #7
    Registered User
    Join Date
    06-26-2012
    Location
    Burlington
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sorting Tabs in workbook using a list

    Its a great thought and i already considered it but that would mean i would need 31 unique colors and have to change the color of 124 tabs which would take forever. I really dont want to spend any time doing this manually, nor do i think i should have to.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,188

    Re: Sorting Tabs in workbook using a list

    This should do the job.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-26-2012
    Location
    Burlington
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sorting Tabs in workbook using a list

    thanks for the code but it does not work. still sorts alpha-numerically (invoice1, invoice12,...invoice2,invoice21...payments1,payments12...payments2,payments21)

  10. #10
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Sorting Tabs in workbook using a list

    Did you look at this link?

    The link covers the following:
    - Sort some or all worksheet by name, in either ascending or descending order.
    - Order the worksheets by the names provided in an array.
    - Order the worksheets in either ascending or descending order based on a cell
    reference in each worksheet.
    - Group worksheet by tab color (Excel 2002 and later only).
    - Order worksheets based on sheet names in a range of cells.

    http://www.cpearson.com/excel/sortws.aspx
    Last edited by K m; 09-06-2012 at 10:00 AM.

  11. #11
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Sorting Tabs in workbook using a list

    I think your original request was that you provide the list in the order you want it. I'm not even sure if your list is always 'invoice' or 'payments' or ...

    Anyway, this may work for you:

    Please Login or Register  to view this content.
    Note that you need to manually create a new sheet called 'SortSheet' and then you need to change the code to be the range of your list
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,188

    Re: Sorting Tabs in workbook using a list

    If you have more than 10 for each then
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1