Solved

Excel 2013 - Sorting worksheet TABS using VBA?

Posted on 2015-02-20
5
283 Views
Last Modified: 2016-02-10
I have an Excel workbook with about 80 tabs (80 worksheets) and would like to sort them alphabetically.   How can this be done.   I'm thinking VBA?
0
Comment
Question by:brothertruffle880
  • 3
5 Comments
 
LVL 12

Assisted Solution

by:Jeff Darling
Jeff Darling earned 250 total points
ID: 40622124
Sort sheets by name

Public Sub SortTabs()


' Get a list of the sheet names
Dim myArr()
ReDim myArr(1 To Worksheets.Count)
Dim iCnt

iCnt = 1

For Each Sheet In Worksheets
 Debug.Print Sheet.Name
 myArr(iCnt) = Sheet.Name
 iCnt = iCnt + 1
Next

' Sort the sheet names
myArrSorted = SortViaWorksheet(myArr)

For i = 1 To UBound(myArrSorted)
 Debug.Print myArrSorted(i)
Next i

' Change the order of sheets
For i = UBound(myArrSorted) To 1 Step -1
Sheets(myArrSorted(i)).Move Before:=Sheets(1)
Next i

End Sub

Public Function SortViaWorksheet(myArr) As Variant
    
    Dim arr()
    Dim WS As Worksheet ' temporary worksheet
    Dim R As Range
    Dim N As Long
    
    arr = myArr
    
    Application.ScreenUpdating = False
    
    ' create a new sheet
    Set WS = ThisWorkbook.Worksheets.Add
    
    ' put the array values on the worksheet
    Set R = WS.Range("A1").Resize(UBound(arr) - LBound(arr) + 1, 1)
    R = Application.Transpose(arr)
    
    ' sort the range
    R.Sort key1:=R, order1:=xlAscending, MatchCase:=False
    
    ' load the worksheet values back into the array
    For N = 1 To R.Rows.Count
        arr(N) = R(N, 1)
    Next N
    
    ' delete the temporary sheet
    Application.DisplayAlerts = False
    WS.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    ' test/debug/confirmation
    For N = LBound(arr) To UBound(arr)
        Debug.Print arr(N)
    Next N
    
    SortViaWorksheet = arr
    
End Function

Open in new window

0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40622985
0
 
LVL 2

Accepted Solution

by:
Rossano Praderi earned 250 total points
ID: 40622992
This is an alternative solution

For i = 1 To Sheets.Count
   For j = 1 To Sheets.Count - 1
' Sort in ascending order.
       If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
' Sort in descending order.
'      If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
          Sheets(j).Move After:=Sheets(j + 1)
       End If
   Next j
Next i

Open in new window


Bregs
Rossano Praderi
0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40623004
Proposed solution supporting custom sorting, such as month names.  Easily customize using your own custom sort order.
EE2015022002.xlsm
0
 

Author Closing Comment

by:brothertruffle880
ID: 40626389
Great Solutions.  Thanks!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now