Solved

Excel 2013 - Sorting worksheet TABS using VBA?

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 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

17 Experts available now in Live!

Get 1:1 Help Now