Solved

Excel 2013 - Sorting worksheet TABS using VBA?

Posted on 2015-02-20
5
315 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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