# Sorting Worksheets According to Region

by Allen Wyatt
(last updated December 7, 2019)

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

Allowing Only Form Field Changes

Word allows you to create forms that other people can use to enter information. One of the last steps normally taken with ...

Discover More

Setting Bracket Clearance Spacing in the Equation Editor

The Equation Editor provided with Word can be very powerful in how it displays mathematic equations. Here's how you can ...

Discover More

Displaying a Live Word Count

You can use Word's built in tools to figure out how many words are in your document. If you want a real-time, constantly ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

##### More ExcelTips (ribbon)

Dynamic Worksheet Tab Names

Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but ...

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

Selecting All Visible Worksheets in a Macro

Do you need your macro to select all the visible worksheets (and just the visible ones)? It's not as easy as it sounds, ...

Discover More
##### Subscribe

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

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 six minus 6?

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.

Offer users a bit of sheet-jumping flexibility. Use an Excel macro to display the Sheet Navigation list, anytime, anywhere.

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

##### 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.