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

 

@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 and back to the Index. There are 3 methods for doing this: Either by Using Functions Or By using a VBA code Or By Using Power Query In this ...
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies