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
Highlighted

@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
Subtotals or Vlookups or Both.
Kwesi_Quartey in Excel on
2 Replies
Excel for Mac - Point as separator in a German Excel
Mr_Geo91 in Excel on
2 Replies
Recovery & Solving the problem
Seve16640 in Excel on
0 Replies
Imported mdb tables not opened in the same Sheet
JozefMJ in Excel on
0 Replies
Excel freezes in secondary monitor
manuelzamora in Excel on
0 Replies