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
Medium Priority
405 Views
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
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
• 3

LVL 13

Assisted Solution

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

' 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
WS.Delete
Application.ScreenUpdating = True

' test/debug/confirmation
For N = LBound(arr) To UBound(arr)
Debug.Print arr(N)
Next N

SortViaWorksheet = arr

End Function
``````
0

LVL 13

Expert Comment

ID: 40622985
0

LVL 2

Accepted Solution

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
``````

Bregs
0

LVL 13

Expert Comment

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

ID: 40626389
Great Solutions.  Thanks!!
0

## Featured Post

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…
###### Suggested Courses
Course of the Month11 days, 11 hours left to enroll