Excel VBA to Sort Worksheets Numerically (5 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to sort worksheets numerically using VBA in Excel? Then, this is the right place for you.

Excel VBA can be used to automate numerous tasks, and sorting worksheets is one of them. When you have a large number of worksheets in your workbook and they are named in numerical sequence, sorting them using VBA can help you save time and improve your productivity.

Using a macro you can easily sort worksheets in ascending or descending order according to your need. To sort worksheets numerically you have to loop through each worksheet in a workbook and within the loop, you will need to compare the names of the worksheets and use a sorting algorithm to sort them in numerical order.

Here, you can see that we have sorted 6 worksheets numerically in ascending order just by running a code. Using this code you can also sort a larger number of worksheets in a workbook.


How to Launch VBA Editor in Excel

To write a VBA code in Excel, you have to launch the VBA editor. For launching this VBA editor, you must need the Developer tab, now if you don’t see the Developer tab then you have to enable the Developer tab.

You can insert your code into a module following the steps given below.

  • Firstly, go to the Developer tab >> click on Visual Basic.

Clicking on Visual Basic to open Microsoft Visual Basic Editor

  • Now, the Microsoft Visual Basic Editor will open.
  • Lastly, to add a module, click on Insert >> Select Module.

Inserting Module


Excel VBA to Sort Worksheets Numerically: 5 Examples

Here, we have a workbook where in 6 different worksheets we have the sales records for 6 consecutive months and we want to sort those worksheets numerically. We will show you 5 different ways to do it using some properties and nested loops in Excel VBA.


1. Sort Worksheets Numerically in Ascending Order Using Count, Name Properties, Move Method, and For Loop

Overview Video of Sorting Worksheets Numerically in Ascending Order Using Count, Name Properties, Move Method, and For Loop

VBA Worksheet is an object that is a collection of worksheets. It has many properties to do different tasks. Such as the Name property returns the name of the worksheet mentioned. Then, we can use the Move method on the worksheet object to move that specific sheet. Sheets are another object which is a collection of charts or worksheets. Count property is used to count the number of members in a collection. Using the Sheets.Count property we can count the number of worksheets present in a workbook.

Now, we will use these properties and methods to sort worksheets numerically in ascending order. To do that follow the steps given below.

  • Firstly, insert the following code into your module.

Code to Sort Worksheets Numerically in Ascending Order Using different Worksheet Properties and For Loop

Sub Ascending_Order()
Application.ScreenUpdating = False
Dim Number_of_Sheets As Integer
'use count property to count the number of worksheets
Number_of_Sheets = Sheets.Count
For i = 1 To Number_of_Sheets - 1
For j = i + 1 To Number_of_Sheets
'use Name property to extract the name of the worksheet
If Sheets(j).Name < Sheets(i).Name Then
'use Move property to move the worksheet
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

Code Breakdown

  • In the beginning, we turned off screen updating to make the code run faster.
  • We declared Number_of_Sheets as an integer and assigned the number of sheets in the workbook by using the Count property.
  • Next, we used a For loop from the first worksheet to the second-to-last worksheet in the workbook.
  • After that, we created a nested loop that runs from the worksheet after the i-th worksheet to the last worksheet in the workbook.
  • We checked if the name of the j-th sheet is less than the name of the i-th sheet using the Name property, then it will move the j-th sheet before the i-th sheet using the Move property.
  • Finally, we turned on the screen update again.
  • Run the code and you will see that it has sorted the worksheets in ascending order.

Note: Using this code you can get the sorting perfect if names are in numeric values. However, if they are alphanumeric it may show some errors. Such as if the sheet names are “Sheet1”, “Sheet2” and “Sheet10” it will sort the sheets as “Sheet1”, “Sheet10”, “Sheet2”.

Read More: Excel VBA to Calculate Active Sheet


2. Use Combined WorkSheet Properties and For Loop to Sort Worksheets Numerically in Descending Order

Using the above properties and loop you can also sort the sheets numerically in descending order. However in this case, instead of using the less than (<) sign, we will use the greater than (>) sign. Go through the steps given below to do that.

  • Insert the following code into your module.

Code to Sort Worksheets Numerically in descending Order Using different Worksheet Properties and For Loop

Sub Descending_Order()
Application.ScreenUpdating = False
Dim Number_of_Sheets As Integer
'use count property to count the number of worksheets
Number_of_Sheets = Sheets.Count
For i = 1 To Number_of_Sheets - 1
For j = i + 1 To Number_of_Sheets
'use Name property to extract the name of the worksheet
If Sheets(j).Name > Sheets(i).Name Then
'use Move property to move the worksheet
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
In the above code, we used almost the same code we used in Example 1. However, here we checked if the name of the j-th sheet is greater than the name of the i-th sheet using the Name property, then it will move the j-th sheet before the i-th sheet using the Move method.
  • Run the code and you will see that the code has sorted the worksheets in descending order.

3. Apply VBA Val Function to Sort Worksheets Numerically in Excel

In this example, we will use the VBA Val function in addition to the code we used in Example 1. It is able to solve the issue we may face while sorting worksheets numerically when the sheet names are alphanumeric. This function will only extract the numeric value from the sheet name and then we can use those numbers to sort the worksheets.

  • Insert the following code into your module.

Code to Apply VBA Val Function to Sort Worksheets Numerically in Excel

Sub Val_Function()
Application.ScreenUpdating = False
Dim Number_of_Sheets As Integer
Number_of_Sheets = Sheets.Count
For i = 1 To Number_of_Sheets - 1
For j = i + 1 To Number_of_Sheets
'Use Val function to find the exact numeric value
If Val(Sheets(j).Name) < Val(Sheets(i).Name) Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
In this code, we used the VBA Val function to extract the exact numeric value from the worksheet name.
  • Now, run the code and you will be able to consider the exact numeric values.

4. Use MsgBox User Input to Sort Worksheet in Ascending or Descending Order

You can also sort your worksheets in ascending or descending order according to your wish using MsgBox. Here, we will use the Yes No Cancel button in the MsgBox to select whether you want to sort the worksheets in ascending or descending order.

Code to Use Msgbox User Input to Sort Worksheet in Ascending or Descending Order

Sub User_Input()
Application.ScreenUpdating = False
Dim Number_of_Sheets As Integer
Dim Selected_Sort_Order As VbMsgBoxResult
'Use MsgBox to get the user input
Selected_Sort_Order = MsgBox( _
"Click Yes for Ascending Order and No for Descending Order", vbYesNoCancel)
Number_of_Sheets = Sheets.Count
For i = 1 To Number_of_Sheets - 1
For j = i + 1 To Number_of_Sheets
'Code to execute when user click on Yes button
If Selected_Sort_Order = vbYes Then
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move Before:=Sheets(i)
End If
'Code to execute when user click on No button
ElseIf Selected_Sort_Order = vbNo Then
If Sheets(j).Name > Sheets(i).Name Then
Sheets(j).Move Before:=Sheets(i)
End If
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

Code Breakdown

  • In the above code, we declared Selected_Sort_Order as VbMsgBoxResult.
  • Then, if you click on the Yes button the code will check if the j-th sheet is less than i-th sheet then it will move the j-th sheet above the i-th
  • Otherwise, if you click on the No button the code will check if the j-th sheet is greater than i-th sheet then it will move the j-th sheet above the i-th

5. Sort Only Selected Worksheets Numerically Using Window.SelectedSheets Property

The Window.SelectedSheets property is used to represent only the selected worksheets in a workbook. Suppose you want to sort some of the worksheets of your workbook, but not all of them. In that case, you can use the following code and sort your sheets numerically. However, if the selected sheets are adjacent to each other it will sort them otherwise it will pop up a MsgBox.

  • To sort only the selected worksheets, first, click on the Ctrl button and then select the sheets you want to sort.
  • Then, insert the following code into your module.

Code to Sort Only Selected Worksheets Numerically

Sub Sort_Selected_Worksheets()
Dim First_Worksheet As Integer, Last_Worksheet As Integer
Dim Descending_Order As Boolean
Descending_Order = False
'using ActiveWindow.SelectedSheets to sort only selected worksheets
If ActiveWindow.SelectedSheets.Count = 1 Then
First_Worksheet = 1
Last_Worksheet = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For i = 2 To .Count
If .Item(i - 1).Index <> .Item(i).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next i
First_Worksheet = .Item(1).Index
Last_Worksheet = .Item(.Count).Index
End With
End If
For j = First_Worksheet To Last_Worksheet
For i = j To Last_Worksheet
If Descending_Order = True Then
If Worksheets(i).Name > Worksheets(j).Name Then
Worksheets(i).Move Before:=Worksheets(j)
End If
Else
If Worksheets(i).Name < Worksheets(j).Name Then
Worksheets(i).Move Before:=Worksheets(j)
End If
End If
Next i
Next j
End Sub

Code Breakdown

Dim First_Worksheet As Integer, Last_Worksheet As Integer
Dim Descending_Order As Boolean
Descending_Order = False

This part of the code declared 3 variables. We used the First_Worksheet and Last_Worksheet variables to determine which worksheets to sort and used the Descending_Order variable to determine the sort order.

If ActiveWindow.SelectedSheets.Count = 1 Then
First_Worksheet = 1
Last_Worksheet = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For i = 2 To .Count
If .Item(i - 1).Index <> .Item(i).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next i
First_Worksheet = .Item(1).Index
Last_Worksheet = .Item(.Count).Index
End With
End If
  • These lines check whether one or more worksheets are selected in the active workbook.
  • If only one worksheet is selected, the First_Worksheet variable is set to 1 and the Last_Worksheet variable is set to Count.
  • If multiple worksheets are selected, the macro checks whether they are contiguous (i.e., adjacent to each other). If they are not contiguous, a message box is displayed, and the macro exits. If they are contiguous, the First_Worksheet variable is set to the index of the first selected worksheet, and the Last_Worksheet variable is set to the index of the last selected worksheet.
For j = First_Worksheet To Last_Worksheet
For i = j To Last_Worksheet
If Descending_Order = True Then
If Worksheets(i).Name > Worksheets(j).Name Then
Worksheets(i).Move Before:=Worksheets(j)
End If
Else
If Worksheets(i).Name < Worksheets(j).Name Then
Worksheets(i).Move Before:=Worksheets(j)
End If
End If
Next i
Next j
  • These lines sort the selected worksheets by name. The outer loop iterates over all the selected worksheets, and the inner loop iterates over the remaining selected worksheets.
  • If Descending_Order is true, it checks whether the name of the i-th worksheet is greater than the name of the j-th If it is, the i-th worksheet is moved before the j-th worksheet using the Move method.
  • If Descending_Order is false, the macro checks whether the name of the i-th worksheet is less than the name of the j-th If it is, the ith worksheet is moved before the jth worksheet.
  • Finally, run the code and your selected worksheets will be sorted numerically.

How to Sort Worksheets Alphanumerically or Alphabetically Using VBA in Excel

If your worksheet names are alphanumeric or alphabetic and you want to sort them according to that, you can use the VBA Ucase function to convert all the characters in the sheet names to uppercase letters. Using this function will make sure that it does not affect the comparison between the sheet names by the case of the letters.

Here, we have the following workbook where the sheet names are alphanumeric. Now using the following code we will sort them alphanumerically.

Image of Worksheets

  • Insert the following code into your module.

Code to use Ucase function to Sort Worksheets Alphanumerically or Alphabetically

Sub Sort_Alphanumerically()
Application.ScreenUpdating = False
Dim Number_of_Sheets As Integer
Number_of_Sheets = Sheets.Count
For i = 1 To Number_of_Sheets - 1
For j = i + 1 To Number_of_Sheets
'use UCase function to sort alphanumerically
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
The above code is mostly like the code we have used for Example 1. However, as we are considering alphanumeric or alphabetic sheet names here we used the UCase function to erase the issue for case.
  • Finally, click on the run button to sort worksheets alphanumerically or alphabetically.

Things to Remember

Sometimes, sorting worksheets may cause a loss of data in charts or formulas that you may have linked with the sheets. So, you can try to back up your data before sorting the worksheets.


Download Practice Workbook

You can download the workbook to practice yourself.


Conclusion

In this article, we tried to show you some easy ways to sort your worksheets numerically in both ascending and descending order in Excel through 5 examples. In addition, we also tried to show you how to sort worksheets if their names are alphanumeric or alphabetic. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo