Solved

# Sort worksheets by their TAB name

Posted on 2011-04-23
Medium Priority
384 Views
Last Modified: 2012-05-11
I have the below code, but what I need is to sort the worksheets by their TAB names, not the internal sheetname.  Sheets are added and taken away during various processes and needs, after which the Table of Contents is refreshed.
``````'Sort Worksheets
For j = 1 To Worksheets.Count - 1
If UCase(Worksheets(j).Name) > UCase(Worksheets(j + 1).Name) Then
Worksheets(j).Move After:=Sheets(j + 1)
End If
Next
``````
0
Question by:ssmith94015
• 2
• 2
• 2
• +1
7 Comments

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 35453483
The routine below sorts the specified tabs in a worksheet by the tab names or a cell value on each worksheet. Tabs are sorted as date values if requested and they can be interpreted as dates, as numeric values if numeric, or as text. Tabs can be sorted in ascending or descending order. See the comments in the routine for more information about the syntax and parameters.

[Begin Code Segment]

Public Sub SortTabs( _
ByVal FirstTab As Variant, _
ByVal LastTab As Variant, _
Optional ByVal Workbook As Workbook, _
Optional DescendingOrder As Boolean, _
Optional InterpretAsDates As Boolean, _
Optional CellAddress As String _
)

' Sort the specified tabs using the tab names or a cell value. Tab names are
' sorted as dates if requested and they can be interpreted as such, or as
' numeric values if numeric, or as text.
'
' Syntax
'
' SortTabs(FirstTab, LastTab, [Workbook], [DescendingOrder], [InterpretAsDates], [CellAddress])
'
' FirstTab - The first tab to be sorted. The tabs to be sorted are specified as
'   a range of tabs. Tabs can be specified as a tab index, a tab name, or a
'   worksheet reference.
'
' LastTab - The last tab to be sorted. See the FirstTab parameter.
'
' Workbook - The workbook containing the tabs to be sorted. Optional. If
'   omitted than the ThisWorkbook object is assumed.
'
' DescendingOrder - Pass True to sort in descending order, False to sort in
'   ascending order. Optional. If omitted then False or ascending order is
'   assumed.
'
' InterpretAsDates - Pass True to interpret the tab names as dates if possible.
'   Optional. If omitted than False is assumed.
'
' CellAddress - Pass a cell address to use instead of the tab names. Optional.
'   If omitted then the tab names are used.
'
' Â© 2007-2010 Kevin Jones

Dim TabNames As Variant
Dim TabSortKeys As Variant
Dim Indices As Variant
Dim Index1 As Long
Dim Index2 As Long
Dim Temp As String

' Normalize parameters
If Workbook Is Nothing Then Set Workbook = ThisWorkbook
If TypeName(FirstTab) = "Worksheet" Then FirstTab = FirstTab.Index
If VarType(FirstTab) = vbString Then FirstTab = Workbook.Sheets(FirstTab).Index
If TypeName(LastTab) = "Worksheet" Then LastTab = LastTab.Index
If VarType(LastTab) = vbString Then LastTab = Workbook.Sheets(LastTab).Index

' Interpret the tab names and prepare them for sorting
TabNames = Array()
TabSortKeys = Array()
For Index1 = FirstTab To LastTab
ReDim Preserve TabNames(LBound(TabNames) To UBound(TabNames) + 1)
TabNames(UBound(TabNames)) = Workbook.Sheets(Index1).Name
ReDim Preserve TabSortKeys(LBound(TabSortKeys) To UBound(TabSortKeys) + 1)
If Len(CellAddress) > 0 Then
TabSortKeys(UBound(TabNames)) = Workbook.Sheets(Index1).Range(CellAddress).Text
Else
If InterpretAsDates And IsDate(Replace(Workbook.Sheets(Index1).Name, "_", " ")) Then
TabSortKeys(UBound(TabNames)) = CDate(Replace(Workbook.Sheets(Index1).Name, "_", " "))
ElseIf IsNumeric(Workbook.Sheets(Index1).Name) Then
TabSortKeys(UBound(TabNames)) = CDbl(Workbook.Sheets(Index1).Name)
Else
TabSortKeys(UBound(TabNames)) = Workbook.Sheets(Index1).Name
End If
End If
Next Index1

' Sort the tab names
ReDim Indices(LBound(TabNames) To UBound(TabNames))
For Index1 = LBound(TabNames) To UBound(TabNames)
Indices(Index1) = Index1
Next Index1
If UBound(Indices) - LBound(Indices) > 0 Then
For Index1 = LBound(Indices) To UBound(Indices) - 1
For Index2 = Index1 + 1 To UBound(Indices)
If DescendingOrder And TabSortKeys(Indices(Index2)) > TabSortKeys(Indices(Index1)) Or Not DescendingOrder And TabSortKeys(Indices(Index2)) < TabSortKeys(Indices(Index1)) Then
Temp = Indices(Index2)
Indices(Index2) = Indices(Index1)
Indices(Index1) = Temp
End If
Next Index2
Next Index1
End If

' Reorder the tabs
For Index1 = LBound(Indices) To UBound(Indices)
Workbook.Sheets(TabNames(Indices(Index1))).Move Workbook.Sheets(FirstTab + Index1 - LBound(TabNames))
Next Index1

End Sub

[End Code Segment]

Kevin
0

LVL 81

Expert Comment

ID: 35453489
Once the above sub is added to your VBA project, your code becomes:

'Sort Worksheets
SortTabs ThisWorkbook.Sheets(1), ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Kevin
0

LVL 7

Expert Comment

ID: 35453642
Can we get the "TAB" names from the sheet?
If possible, then we can modify the code, replace sheet(xxx).name as "TAB" name.
'Sort Worksheets
For j = 1 To Worksheets.Count - 1
If UCase(TAB name from sheet(j)) > UCase(TAB name from sheet(j+1)) Then
Worksheets(j).Move After:=Sheets(j + 1)
End If
Next
0

LVL 42

Expert Comment

ID: 35453766
@huacat - yes you can:  Good one!  However, if the tab names are more sophisticated (e.g., date's, etc.) then I might lean more toward Zorvek's solution, personally...

Sub sortTabNames()

For j = 1 To Worksheets.Count - 1
If UCase(Sheets(j).Name) > UCase(Sheets(j + 1).Name) Then
Worksheets(j).Move After:=Sheets(j + 1)
End If
Next j

End Sub
0

LVL 42

Expert Comment

ID: 35453769
PS - my above post not for points....

Dave
0

Author Comment

ID: 35454563
Thank you all.  Will have to try this on Monday as we just had a major shut-down.  Realy did not want to work today anyway!
0

Author Closing Comment

ID: 35454856
Kevin, had to finish this before I left and this is working.  Thank you, I did not think it woudl be this complex, but names are mixed with dates as well as text - also, the master workbooks need to be sorted and this worked in both cases that I had.

Sandra
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month12 days, 15 hours left to enroll

#### 578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.