Sorting Sheets In Ascending Order through VBA In Microsoft Excel 2010

 

In this article, we will learn sorting sheets in ascending order through VBA in Microsoft Excel 2010.

While working on multiple sheets, you find that sheets are not properly arranged. You want to create a macro quickly which will automatically sort the sheet in ascending order whenever macro is run.
 
img1
 
To sort the sheets in ascending order, follow the below mentioned steps:
 

  • Press Alt+F11 to launch VB Editor screen
  • From the Insert menu, select Module.

 
img2
 

  • In the Module, type the code lines as follows:

 

Sub SortingSheetsInAscending()

Dim i As Integer, n As Integer, SheetsCounter As Integer

 

If ActiveWorkbook Is Nothing Then Exit Sub

If ActiveWorkbook.ProtectStructure Then

MsgBox ActiveWorkbook.Name & ” is protected”, vbCritical, “Sort Sheets”

Exit Sub

End If

 

If MsgBox(“Sort Sheets?”, vbQuestion + vbYesNo) <> vbYes Then Exit Sub

 

Application.EnableCancelKey = xlDisabled

SheetsCounter = Sheets.Count

 

For i = 2 To SheetsCounter

For n = 1 To SheetsCounter

If Sheets(n).Name > Sheets(i).Name Then

Sheets(i).Move before:=Sheets(n)

End If

Next n

Next i

End Sub

 
img3
 

  • Run the macro from the Module by pressing F5.

Alternatively, press Alt+F11 and return to Excel, assign the macro to any Excel object as a button or icon, or press Alt+F8 to select the macro, and then click Run.
 
You will find that the sheets are arranged in an alphanumeric order.
 
img4
 
 



One thought on “Sorting Sheets In Ascending Order through VBA In Microsoft Excel 2010

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>