Written by Allen Wyatt (last updated June 18, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Koen has a workbook containing 150 worksheets, one for each company branch. He needs to sort those sheets based on the region to which each branch belongs. (There are five regions in his company.) He also notes that it would be great if the tab color for each worksheet could reflect the region, and wonders if such sorting is possible in Excel.
Yes, such sorting is possible, but it does require the use of a macro. The hardest part of creating the macro is defining how you determine which branches are in which region. Perhaps the simplest way to do this is to make sure your worksheets use a naming pattern that includes both the region and branch information. For instance, you might name the worksheets something like "Reg01-Branch123." Your macro could then step through each worksheet and do both the coloring and the sorting.
Sub SortWorksheets() Dim iReg As Integer Dim I As Integer Dim J As Integer Dim K As Integer Dim ws As Worksheet Application.ScreenUpdating = False ' Set tab colors For Each ws in Worksheets iReg = Val(Mid(ws.Name,4,2)) Select Case iReg Case 1 ws.Tab.Color = vbRed Case 2 ws.Tab.Color = vbYellow Case 3 ws.Tab.Color = vbBlue Case 4 ws.Tab.Color = vbGreen Case 5 ws.Tab.Color = vbCyan Case Else ws.Tab.ColorIndex = xlColorIndexNone End Select Next ws ' Sort the worksheets For I = 1 To Sheets.Count - 1 K = I For J = I + 1 To Sheets.Count If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J Next J If K <> I Then Sheets(K).Move Before:=Sheets(I) Next I Application.ScreenUpdating = True End Sub
The macro works through the Worksheets collection twice. On the first pass through, the region number is extracted from the worksheet's name. This is then used (in the Select Case structure) to set the tab color. If the region number is outside the range of 1-5, then no change is made to the tab color.
If your determination of what branch is in what region is more complex, then you'll need to adjust the macro accordingly. For instance, you might have a worksheet named "Region Key" that contains, in column A, the names of each of your branches and, in column B, the corresponding region number for each of those branches. Assuming that the actual data begins in row 2, you could modify the macro in the following manner:
Sub SortWorksheets2() Dim sTemp As String Dim iReg As Integer Dim I As Integer Dim J As Integer Dim K As Integer Dim ws As Worksheet Dim key As Worksheet Application.ScreenUpdating = False Set key = Worksheets("Region Key") ' Set tab colors For Each ws in Worksheets sTemp = UCase(ws.Name) I = 2 ' Beginning row number iReg = 0 While key.Cells(I, 1) > "" If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2) I = I + 1 Wend Select Case iReg Case 1 ws.Tab.Color = vbRed Case 2 ws.Tab.Color = vbYellow Case 3 ws.Tab.Color = vbBlue Case 4 ws.Tab.Color = vbGreen Case 5 ws.Tab.Color = vbCyan Case Else ws.Tab.ColorIndex = xlColorIndexNone End Select Next ws ' Sort the worksheets For I = 1 To Sheets.Count - 1 K = I For J = I + 1 To Sheets.Count If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J Next J If K <> I Then Sheets(K).Move Before:=Sheets(I) Next I Sheets("Region Key").Move Before:=Sheets(1) Application.ScreenUpdating = True End Sub
The biggest difference between this macro and the previous one is that this one grabs the region number from a worksheet. As the last action in the macro, the "Region Key" worksheet is moved to the very beginning of the Worksheets collection.
Note that this second macro can also result in your worksheet tabs, at the bottom of the program window, looking like a kaleidoscope of colors. The reason for this is that the tabs are sorted based on their names, not on their colors. This differs from the first macro, which effectively sorted by region and then by branch because the worksheets were named using that pattern. If you want to still use the "Region Key" approach and sort by region and then branch, you can do so by adjusting the macro a bit more:
Sub SortWorksheets3() Dim sTemp As String Dim sSortArray(499) As String Dim iReg As Integer Dim I As Integer Dim J As Integer Dim K As Integer Dim ws As Worksheet Dim key As Worksheet Application.ScreenUpdating = False Set key = Worksheets("Region Key") ' Set tab colors and build sort array J = 0 For Each ws in Worksheets sTemp = UCase(ws.Name) I = 2 ' Beginning row number iReg = 0 While key.Cells(I, 1) > "" If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2) I = I + 1 Wend J = J + 1 sSortArray(J) = Right("00" & iReg, 2) & " " & ws.Name Select Case iReg Case 1 ws.Tab.Color = vbRed Case 2 ws.Tab.Color = vbYellow Case 3 ws.Tab.Color = vbBlue Case 4 ws.Tab.Color = vbGreen Case 5 ws.Tab.Color = vbCyan Case Else ws.Tab.ColorIndex = xlColorIndexNone ' Force into incorrect region area for sort sSortArray(J) = "00 " & ws.Name End Select Next ws ' Sort the worksheets For I = 1 To Sheets.Count - 1 K = I For J = I + 1 To Sheets.Count If UCase(sSortArray(K)) > UCase(sSortArray(J)) Then K = J Next J If K <> I Then Sheets(K).Move Before:=Sheets(I) sTemp = sSortArray(K) For J = K To I Step -1 sSortArray(J) = sSortArray(J-1) Next J sSortArray(I) = sTemp End If Next I Sheets("Region Key").Move Before:=Sheets(1) Application.ScreenUpdating = True End Sub
Note that this iteration of the macro relies upon a helper array (sSortArray) to keep track of how the names in the worksheets should be sorted.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13710) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Want a quick way to combine your worksheets? This tip features a simple macro to do the task for you.
Discover MoreWant to make fast work of moving from one worksheet to another? Here's how to do the task when you have a lot of ...
Discover MoreIf you are using a macro to process a number of worksheets, you may have a need to know if the worksheet is empty or not. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-12-07 16:26:21
Ron S MVP
And I forgot to mention Allen's PivotTable book <G>. I saw the ad for it after posting my previous reply and I scrolled down in the weekly newsletter.
2019-12-07 16:22:25
Ron S MVP
I like to see as many tabs as possible, so I would make the tab names shorter, ie R1B001 or R1 B001
.
I made a couple of other suggestions that Allen didn't accept, so here they are.
With 150 sheets that is a lot of scrolling to get around between the tabs.
Using colors and sorted 'logical' tab names (what ever logic works best for you) is one way to help with that.
Another way to make that easier is to add a macro generated "TOC" / Table of Contents page. The TOC has hyperlinks to all of the pages. You add a TOC Hyperlink to the same spot on each page, ie cell A1 (?) so you can easily jump to the TOC no matter what tab you are on
Here are links to a few articles that show how to do that.
“Too Many Worksheets” Driving You Nuts? Read This! Feb 16, 2009
https://chandoo.org/wp/2009/02/16/excel-table-of-contents-etc/
Learn how to create Table of Contents in Excel and other tricks to manage when you have too many worksheets.
Add a macro that displays the Excel Worksheet Navigation list
https://www.techrepublic.com/blog/microsoft-office/add-a-macro-that-displays-the-excel-worksheet-navigation-list/
Offer users a bit of sheet-jumping flexibility. Use an Excel macro to display the Sheet Navigation list, anytime, anywhere.
Build Table of Contents and similar listings, and working with Hyperlinks
http://dmcritchie.mvps.org/excel/buildtoc.htm
Create a Table of Contents with hyperlinks to the other sheets in your workbook using the BuildTOC() macro. The BuildTOC macro will sort the names for you. (Hyperlinks were introduced in XL97). $$TOC is the suggested sheetname for this content. Additional macros relating to documentation and organization can also be found here including listing addins, functions and macros. Also have included information on creating and viewing hyperlinks individually and how to remove (delete) them singly or from a range.
That is the "simple" fix. Add a couple of macros and the hyper links to the TOC and you are done in 5-10 minutes.
Another alternative, much more work initially, is to use the Excel Table (and maybe PivotTable) feature. You moosh all of the tabs into one tab and one table. It will require adding at least a couple of columns to each row, ie Branch and region. You can auotmate that process by using the PowerQuery feature. Then you can use a slicer for Branch, and maybe Region, and maybe a date based one (months &/or years). This way everything is on the same tab. You just use the slicer to pick the branch (and time span?) you want to view. Adding new data is trivial, just add it to the bottom of the table. Then you can use this table as input to a PivotTable to generate summary reports of totals and various statistics that PivotTables are really good at.
Microsoft Power Query for Excel Help
https://support.office.com/en-us/article/microsoft-power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94
This is MS home page for PowerQuery help, with links to MANY detailed help pages
Power Query provides data discovery, data transformation and enrichment for the desktop to the cloud.
Easily discover, combine, and refine data for better analysis in Excel.
Get & Transform and Power Pivot in Excel
https://support.office.com/en-us/article/get-transform-and-power-pivot-in-excel-42d895c2-d1d7-41d0-88da-d1ed7ecc102d
With Get & Transform and Power Pivot in Excel, you'll spend less time manipulating data, and more time driving impact. Watch this 2-minute video to learn how. Whether it's small data sets or large ones with up to millions of rows, you can connect, clean, analyze, and share your data faster. This 10-minute learning guide will show you how.
Create a Table in Excel 2010
http://www.dummies.com/how-to/content/how-to-create-a-table-in-excel-2010.seriesId-223718.html
You can create a table in Excel 2010 to help you manage and analyze related data. The purpose of an Excel table is not so much to calculate new values but rather to store lots of information in a consistent …
Overview of Excel tables
https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list).
! Excel Pivot Tables Tips (also has related links for Overview, Why Pivot, Examples)
https://exceljet.net/excel-pivot-tables
Pivot tables are a reporting engine built into Excel. They are the single best tool in Excel for analyzing data without formulas. You can create a basic pivot table in about one minute, and begin interactively exploring your data.
Create a PivotTable to analyze worksheet data
https://support.office.com/en-us/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.
Pivot Table in Excel (Complete Guidelines)
https://yodalearning.com/tutorials/pivot-tables-complete-guidelines/
When you have a large amount of data in your worksheet and you can’t analyze or sort the data then Pivot Table in Excel helps you to carry out the task easily and more conveniently. Pivot Tables are powerful functions in excel. We are going to start with what is Pivot Table in Excel and explain its uses, types, importance, creation and much more.
If you want more help about PivotTables you can ask questions on the MS "Answers" forum and get free help from volunteers literally from around the world.
https://answers.microsoft.com/en-us/msoffice/forum/
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments