Sep 02 2019 11:28 PM
Good day! Please tell me if Excel has a function for sorting worksheets
Sep 03 2019 12:00 AM
If you are talking about Sorting Sheet Tabs in Ascending or Descending order, you may place the following Macros on a Standard Module like Module1 and run them as per your requirement.
To Sort Sheet Tabs in Ascending Order:
Sub SortSheetTabsAscending()
Dim ws As Worksheet
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If Sheets(j).Name > Sheets(j + 1).Name Then
Sheets(j).Move After:=Sheets(j + 1)
End If
Next j
Next i
ws.Activate
Application.ScreenUpdating = True
End Sub
To Sort Sheet Tabs in Descending Order:
Sub SortSheetTabsDescending()
Dim ws As Worksheet
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If Sheets(j).Name < Sheets(j + 1).Name Then
Sheets(j).Move After:=Sheets(j + 1)
End If
Next j
Next i
ws.Activate
Application.ScreenUpdating = True
End Sub
Sep 03 2019 01:48 AM - edited Sep 03 2019 02:06 AM
Hi
Sorting the physical sheets can only be done by a Code (as you need to perform an action)... and a nice code has already been provided by a colleague.
However, if you want to consider creating a summary sheet (like an Index of Sheets) for all the worksheets sorted alphabetically and be able to navigate back and forth with a click, then the easiest option is to do it by Power Query. If Sheets are added or deleted then, just refresh the query.
Here is a simple tutorial in which I explain How to do It:
https://www.youtube.com/watch?v=jEr3uj1ljQ8
In the description below the video you have links to my other tutorials in which I do the same exact thing either using VBA or by using Functions.
Hope that helps
Nabil Mourad