• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Sort worksheets by their TAB name

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

Open in new window

0
Sandra Smith
Asked:
Sandra Smith
  • 2
  • 2
  • 2
  • +1
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
huacatCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
dlmilleCommented:
@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
 
dlmilleCommented:
PS - my above post not for points....

Dave
0
 
Sandra SmithRetiredAuthor Commented:
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
 
Sandra SmithRetiredAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now