If you are used to the ease at which you can sort data within a worksheet you might find it slightly surprising that you can’t easily sort your worksheets within a workbook. When I say “easily” what I actually mean is that it is impossible to sort worksheets unless you employ some VBA. This tutorial assumes you know the basics about writing macros and are able to navigate the VBA environment.
First step is to create a VBA Userform – see example below. This is pretty easy to achieve: in the VBA environment click Insert > Userform. You should then be able to add your controls, labels and button. You will need the Toolbox and Properties window open to make your form. I named my sort button SortButton. You need to name you’re A-Z option button asc for it to work with the code.
Next double-click on the Sort button and add this code to the code window.
Private Sub SortButton_Click() 'declare two variables one for the current sheet position 'and one for the previous sheet position Dim cs As Integer Dim ps As Integer 'if the ascending option has been selected on the userform If asc = True Then 'set two counters one for the current and one for the previous sheet For cs = 1 To Sheets.count For ps = 1 To cs - 1 'if the previous sheet name is alphabetically after the current sheet name, '- move the current sheet before the previous sheet position If UCase(Sheets(ps).Name) > UCase(Sheets(cs).Name) Then Sheets(cs).Move before:=Sheets(ps) End If Next ps Next cs Else: 'if the descending option has been selected on the userform For cs = 1 To Sheets.count For ps = 1 To cs - 1 'if the previous sheet name is alphabetically before the current sheet name, '- move the current sheet before the previous sheet position If UCase(Sheets(ps).Name) < UCase(Sheets(cs).Name) Then Sheets(cs).Move before:=Sheets(ps) End If Next ps Next cs End If End Sub
In a new or existing module enter this macro to open the userform. I called my form SortWorksheets.
Sub sortshtsform() SortWorksheets.Show End Sub
Up to you how you decide to run the macro, but a button on the Quick Access Toolbar works quite well.