Solved

Excel 2013 - Sorting worksheet TABS using VBA?

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

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

628 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