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.
Sorting tabs by color and alphabetically
- Thread is marked as Resolved.
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).
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).
Hi Jeff, I completely understand. What is obvious to me may not be obvious to someone reading my post. Thank you for your input. Yes, each tab represents an employee. Option Explicit Sub test() Dim ws As Worksheet, myColor, x, i As Long, ii As Long Dim myList, w(), n As Long myColor = VBA.Array(208) Application.ScreenUpdating = False ReDim myList(1 To UBound(myColor) + 1) For Each ws In Sheets x = Application.Match(ws.Tab.Color, myColor, 0) If IsError(x) Then ReDim Preserve myColor(UBound(myColor) + 1) ReDim Preserve myList(1 To UBound(myColor) + 1) myColor(UBound(myColor)) = ws.Tab.Color x = UBound(myColor) + 1 End If If Not IsArray(myList(x)) Then ReDim w(1 To 2, 1 To 1) Else w = myList(x) ReDim Preserve w(1 To 2, 1 To UBound(w, 2) + 1) End If w(1, UBound(w, 2)) = IIf(IsNumeric(ws.Name), Val(ws.Name), ws.Name) w(2, UBound(w, 2)) = ws.Name myList(x) = w Next For i = 1 To UBound(myList) If IsArray(myList(i)) Then mySort myList(i), 1 End If Next For i = 1 To UBound(myList) If IsArray(myList(i)) Then For ii = 1 To UBound(myList(i), 2) Sheets(myList(i)(2, ii)).Move after:=Sheets(Sheets.Count) Next End If Next End Sub Private Sub mySort(a, ref) Dim i As Long, ii As Long, iii As Long, temp For i = LBound(a, 2) To UBound(a, 2) - 1 For ii = i + 1 To UBound(a, 2) If a(ref, i) > a(ref, ii) Then For iii = LBound(a, 1) To UBound(a, 1) temp = a(iii, i) a(iii, i) = a(iii, ii) a(iii, ii) = temp Next End If Next Next End Sub
Your code is a little dense and with no comments, so I don't understand the algorithm.
I'm trying to unravel this:
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.
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?
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?
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.
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
Here is a version that suppresses screen updates so may run a little faster.
Worked perfectly. Thank you so much.
I have an accrual formula post out there if you have time or want to take a look at it.
Don’t have an account yet? Register yourself now and be a part of our community!