?
Solved

Sort worksheets by their TAB name

Posted on 2011-04-23
7
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

Open in new window

0
Comment
Question by:ssmith94015
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 81

Accepted Solution

by:
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

by:zorvek (Kevin Jones)
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

by:huacat
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dlmille
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

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

Dave
0
 

Author Comment

by:ssmith94015
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

by:ssmith94015
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

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.

Join & Ask a Question