Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2013 - Sorting worksheet TABS using VBA?

Posted on 2015-02-20
5
Medium Priority
?
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 13

Assisted Solution

by:Jeff Darling
Jeff Darling earned 1000 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 13

Expert Comment

by:Jeff Darling
ID: 40622985
0
 
LVL 2

Accepted Solution

by:
Rossano Praderi earned 1000 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 13

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

730 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