Skip to content

Sort Worksheets Alphabetically in Excel with a Macro

    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.

    sort sheets dialog box

    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.

    Leave a Reply