Sorting Worksheets According to Region

by Allen Wyatt
(last updated December 7, 2019)

2

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:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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 Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Accessing Old Excel Data

If you have some legacy data stored in very old spreadsheet files, you may be wondering how to get that data into an ...

Discover More

Using Named Formulas or Constants

An easy way to create a name for a formula or constant value. The name can then be used in other formulas or for ...

Discover More

Opening and Printing a Document

Want a quick, fast way to print a Word document? Here's a way that can't be beat!

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Referencing a Worksheet Name

Excel provides ways to reference the column or row number of a cell, but it doesn't provide a built-in way to reference a ...

Discover More

Determining the Complexity of a Worksheet

If you have multiple worksheets that each provide different ways to arrive at the same results, you may be wondering how ...

Discover More

Quickly Copying Worksheets

Excel provides a little-known way to copy worksheets simply by clicking and dragging. Here's how to do it.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 - 8?

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/


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.