Sorting tabs by color and alphabetically

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello! I saw your post regarding sorting tabs by color and alphabetically. It helped tremendously. Thank you. However, I am having trouble figuring out how to specify the color order. I track our employee's PTO in excel and color code then by active, term and if they change status to no longer eligible for PTO. I played around with changing the tab colors, but my terms are still showing up at the beginning of the workbook. Any help is much appreciated.

    I see this has been open for a while. If you still need help, please clarify what result you want.

    "my terms are still showing up at the beginning of the workbook"

    It's not clear what you want to happen, or why this is the wrong result. Please give a more detailed explanation--what do "active" and "term" mean, and how are you color coding for them, and what order do you want the tabs in?

    Also please attach a sample file, or at least post your VBA here (using code tags).

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    Hi Jeff, The tab colors are as such: active employees: blue - termed employees: red - other: light green. I wanted to have the workbook sort alphabetically within the color group, but I wanted to see if I could pick the colors in the order in which they show in the workbook. This is the order I wanted them to appear in the workbook; active, other and terms. I ended up changing the tab colors so that so I get this order. I'm sure there is another way, but this is working for me. I am very new to working with VBA. Hopefully, I don't need to add another group color. :) Thank you for responding to my post.

    I think this is pretty straightforward but there are a couple of details missing from your description. For example, does each tab represent one employee? That's the kind of thing that is obvious to you, but if you read your post you'll realize that we have no idea. Also, when setting the color of a tab, how to determine the category for that tab?

    The best thing is to attach your file. If it has personal data and you can't post it, then at least paste into a post all of the code that you are using now (mark it as Code with the code button so it's readable).

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    Your code is a little dense and with no comments, so I don't understand the algorithm.


    I'm trying to unravel this:

    Quote from KEW115

    The tab colors are as such: active employees: blue - termed employees: red - other: light green..... This is the order I wanted them to appear in the workbook; active, other and terms. I ended up changing the tab colors so that so I get this order.

    Do you mean that these are not really the colors you would prefer to use, but you are using these colors just so the sorting works?

    I am having a hard time reverse engineering your code. It is better to use variable names that offer meaning, rather than x, n, w, ii, and so forth. It ends up being rather cryptic. I would do this from scratch and first identify the desired colors and what order you want them, then sort within each color group. So if you tell me what tab colors you really want to use, I'll start from there. Specify RGB colors, not just color names.

    Also, are there any worksheets that are some kind of master sheet or summary or something that you don't want included in the sort?

    How many tabs do you have? Unless performance is an issue, I would probably use an insertion sort. It looks at first glance like you are using a bubble sort.

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    There are two tabs that are masters/summary sheets. I did not color code them. I would like them to appear at the beginning of the workbook. So I guess that would mean not to include them in the sort. Color sort order I would prefer:


    1st color RGB Red: 112 Green: 48 Blue: 160

    2nd color RGB Blue: 254

    3rd color RGB Red: 225


    Currently there are 246 tabs. Since our turnover rate is rather high, I am sure the tabs will increase throughout the year.


    I truly appreciate your time and help. Thank you so much :)

    Do those colors correspond in that order to active, other, terminated?

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    Is the color coding the only way to determine the status? Is there anything on the worksheet that could indicate it? If so, how do you ensure they are in sync? Do you color the tabs manually?

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    I'm also assuming that your master sheets do not have a tab color that matches the other three colors. That is how I'm going to identify which ones to ignore.

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    Here is the code in a test workbook. It is very easy to understand how this code works. It writes the tab names to a temporary worksheet then uses a worksheet sort to sort them, rather than building a sort algorithm in VBA. This is not super fast. For 246 tabs it might take a few minutes to run. Moving tabs is time-consuming.KEW115=sort tabs.xlsm

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    I have an accrual formula post out there if you have time or want to take a look at it. :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login