Sorting tabs


Is there a way to sort the sheet tabs within a workbook using a macro?



Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1492) Apr 20, '17 at 9:40 am
Add to Discussion



We literally have that macro here on teachexcel.

Sort Excel Worksheet Tabs

That page provides the code and a desription of it.

As an alternative to the above, this macro should be installed in the ThisWorkbook code module and runs automatically whenever you insert a new sheet. It will prompt for a sheet name (suggesting Excel's default) and sort it into its correct location (sorting all other sheets as well in the process, if they weren't sorted before).
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    ' 14 Apr 2017
    Const FirstToSort As Integer = 1
    Dim ShName As String
    Dim i As Integer
    Dim Done As Boolean
    Application.ScreenUpdating = False
    With ActiveSheet
            ShName = .name
            ShName = InputBox("What is this worksheet's name?", "Give a name", ShName)
            If Len(ShName) = 0 Then ShName = .name
            On Error Resume Next
            .name = ShName
        Loop While Err
    End With
    With ThisWorkbook.Worksheets
            Done = True
            For i = FirstToSort To .Count - 1
                If .Item(i).name > .Item(i + 1).name Then
                    .Item(i + 1).Move Before:=.Item(i)
                    Done = False
                End If
            Next i
        Loop Until Done
    End With
    Application.ScreenUpdating = True
End Sub
Observe the code line "Const FirstToSort As Integer = 1". It determines that all sheets should be sorted. If your workbook has a summary sheet, for example, that should always be the first tab you can set the constant to 2 (or 3 or any number) to exclude those sheets from the sort.

Answer the Question

You must create an account to use the forum. Create an Account or Login