Home

Sort worksheets in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-834044%22%20slang%3D%22en-US%22%3ESort%20worksheets%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834044%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EGood%20day!%20Please%20tell%20me%20if%20Excel%20has%20a%20function%20for%20sorting%20worksheets%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-834044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834070%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20worksheets%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402385%22%20target%3D%22_blank%22%3E%40G_Elena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20talking%20about%20Sorting%20Sheet%20Tabs%20in%20Ascending%20or%20Descending%20order%2C%20you%20may%20place%20the%20following%20Macros%20on%20a%20Standard%20Module%20like%20Module1%20and%20run%20them%20as%20per%20your%20requirement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3ETo%20Sort%20Sheet%20Tabs%20in%20Ascending%20Order%3A%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20SortSheetTabsAscending()%0ADim%20ws%20As%20Worksheet%0ADim%20i%20As%20Integer%2C%20j%20As%20Integer%0A%0AApplication.ScreenUpdating%20%3D%20False%0ASet%20ws%20%3D%20ActiveSheet%0AFor%20i%20%3D%201%20To%20Sheets.Count%0A%20%20%20For%20j%20%3D%201%20To%20Sheets.Count%20-%201%0A%20%20%20%20%20%20%20%20%20If%20Sheets(j).Name%20%26gt%3B%20Sheets(j%20%2B%201).Name%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Sheets(j).Move%20After%3A%3DSheets(j%20%2B%201)%0A%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20Next%20j%0ANext%20i%0Aws.Activate%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3ETo%20Sort%20Sheet%20Tabs%20in%20Descending%20Order%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20SortSheetTabsDescending()%0ADim%20ws%20As%20Worksheet%0ADim%20i%20As%20Integer%2C%20j%20As%20Integer%0AApplication.ScreenUpdating%20%3D%20False%0ASet%20ws%20%3D%20ActiveSheet%0AFor%20i%20%3D%201%20To%20Sheets.Count%0A%20%20%20For%20j%20%3D%201%20To%20Sheets.Count%20-%201%0A%20%20%20%20%20%20%20%20%20If%20Sheets(j).Name%20%26lt%3B%20Sheets(j%20%2B%201).Name%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Sheets(j).Move%20After%3A%3DSheets(j%20%2B%201)%0A%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20Next%20j%0ANext%20i%0Aws.Activate%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834126%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20worksheets%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402385%22%20target%3D%22_blank%22%3E%40G_Elena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3ESorting%20the%20physical%20sheets%20can%20only%20be%20done%20by%20a%20Code%20(as%20you%20need%20to%20perform%20an%20action)...%20and%20a%20nice%20code%20has%20already%20been%20provided%20by%20a%20colleague.%3C%2FP%3E%3CP%3EHowever%2C%20if%20you%20want%20to%20consider%20creating%20a%20summary%20sheet%20(like%20an%20Index%20of%20Sheets)%20for%20all%20the%20worksheets%20%3CSTRONG%3Esorted%20alphabetically%20and%20be%20able%20to%20navigate%20back%20and%20forth%3C%2FSTRONG%3E%26nbsp%3Bwith%20a%20click%2C%20then%20the%20easiest%20option%20is%20to%20do%20it%20by%20Power%20Query.%20If%20Sheets%20are%20added%20or%20deleted%20then%2C%20just%20refresh%20the%20query.%3CBR%20%2F%3EHere%20is%20a%20simple%20tutorial%20in%20which%20I%20explain%20How%20to%20do%20It%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DjEr3uj1ljQ8%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DjEr3uj1ljQ8%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%26nbsp%3B%20description%20below%20the%20video%20you%20have%20links%20to%20my%20other%20tutorials%20in%20which%20I%20do%20the%20same%20exact%20thing%20either%20using%20VBA%20or%20by%20using%20Functions.%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
G_Elena
Occasional Visitor

Good day! Please tell me if Excel has a function for sorting worksheets

2 Replies

@G_Elena 

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

 

Highlighted

@G_Elena 

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

When you have a workbook with lots of worksheets it's very useful to create an Index of All Sheet Names and be able to Navigate to any of these worksheets an...
Related Conversations
Getting #Value!
ranjit995 in Excel on
2 Replies
pratica 1002380750 - query web
Ben_Hur in Excel on
1 Replies
Custom Sheet View is not working in Office 365
RonanK in Excel on
1 Replies
help with macris & VBA
Adil_813 in Excel on
0 Replies
How to count average in certain time range
rutilusss in Excel on
1 Replies
Subtotals or Vlookups or Both.
Kwesi_Quartey in Excel on
7 Replies