Sort the worksheets in a workbook using VBA in Microsoft Excel

In this article, we have provided details about how to sort the worksheets in the workbook in the ascending or descending order, depending upon the worksheet name.

In the attached workbook, there are two modules:-

Module1 contains the macro “SortWorksheets” which is assigned to “Submit” button on the “Macro” sheet.

This macro sorts all the worksheets except the first worksheet which is the main sheet named as “Macro”.

When the user clicks on the “Submit” button, all the sheets after “Macro” sheet will be sorted in ascending or descending order as chosen by user in the combo box.

Module2 contains two macros “AscendingSortOfWorksheets” and “DecendingSortOfWorksheets”. On running “AscendingSortOfWorksheets” macro, it will sort all the worksheets present in the workbook in the ascending order. On running “DecendingSortOfWorksheets” macro, it will sort all the worksheets present in the workbook in the descending order.

Logic explanation

Bubble sort algorithm is used for sorting the worksheets in the workbook.

Bubble sort algorithm compares each item in the array with other item in the array and swaps them depending whether to sort in ascending or descending order. In each step, it will bubble the smallest or largest value to top of the array. Algorithm repeats until all the items are sorted.

In this article, our goal is sort the worksheets in the workbook by their name. Workbook which we are using consists of different worksheets with names like Financial Dashboard, Human Resources, and Sales Dashboard.

To achieve this goal, we have used Bubble sort algorithm for sorting the worksheets.

Code explanation

SortWorksheets Macro

This macro will run when user clicks on the submit button. This macro will sort all the worksheets in the workbook by their names except the first worksheet in the workbook. Depending upon the value selected by the user in the combo box, it will sort the worksheets in ascending or descending order.

For adding combo box, follow below steps:-

1. Go to Developer tab > Click on Insert > Click combo box icon under form control

1. Right click the combo box and click on format control.

1. Go to control tab, Assign the item and link the combo box with cell XFC1.

ComboBoxValue = Range("XFC1").Value

Combo box on the “Macro” worksheet is linked with cell XFC1. So, for getting value from cell XFC1, ComboBoxValue variable is used.

For i = 2 To SCount – 1

FOR loop begins with variable i, value starting from 2 as we want to exclude first worksheet of the workbook from sorting.

If ComboBoxValue = 1 Then

IF condition is used for checking whether to sort in ascending or descending order. IF ComboBoxValue value is 1 then sort in ascending order otherwise descending order.

Worksheets sorted in ascending order

Worksheets sorted in descending order

AscendingSortOfWorksheets and DecendingSortOfWorksheets

AscendingSortOfWorksheets and DecendingSortOfWorksheets macros can be run by pressing Alt + F8 shortcut key or Go to Developer tab > Click on Macro > selecting the macro and click on run. They will sort all the worksheets present inside the workbook. These macros can be run on other workbooks also.

Worksheets sorted after running AscendingSortOfWorksheets macro

Worksheets sorted in ascending order DecendingSortOfWorksheets macro

Please follow below for the code

Sub AscendingSortOfWorksheets()

'Sort worksheets in a workbook in ascending order

Dim SCount, i, j As Integer

'For disabling screen updates
Application.ScreenUpdating = False

'Getting total no. of worsheets in workbook
SCount = Worksheets.Count

'Checking condition whether count of worksheets is greater than 1, If count is one then exit the procedure
If SCount = 1 Then Exit Sub

'Using Bubble sort as sorting algorithm
'Looping through all worksheets
For i = 1 To SCount - 1

    'Making comparison of selected sheet name with other sheets for moving selected sheet to appropriate position
    For j = i + 1 To SCount
        If Worksheets(j).Name < Worksheets(i).Name Then
            Worksheets(j).Move Before:=Worksheets(i)
        End If
    Next j
Next i


End Sub

Sub DecendingSortOfWorksheets()

'Sort worksheets in a workbook in descending order

Dim SCount, i, j As Integer

'For disabling screen updates
Application.ScreenUpdating = False

'Getting total no. of worsheets in workbook
SCount = Worksheets.Count

'Checking condition whether count of worksheets is greater than 1, If count is one then exit the procedure
If SCount = 1 Then Exit Sub

'Using Bubble sort as sorting algorithm
'Looping through all worksheets
For i = 1 To SCount - 1

    'Making comparison of selected sheet name with other sheets for moving selected sheet to appropriate position
    For j = i + 1 To SCount
        If Worksheets(j).Name > Worksheets(i).Name Then
            Worksheets(j).Move Before:=Worksheets(i)
        End If
    Next j
Next i


End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com