Sort Tabs by last character in Tab name (not sheet name)

MarqyMarq

New Member
Joined
Oct 22, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
I know how to sort the tabs by A-Z, and by color. Can you show me how to sort by the last character of the tab name (not the property: sheet name).
I have the first 7 tabs will not be sorted (i call them ADMIN tabs) , but the remaining tabs need to be sorted by the last character (grade).
1627846732933.png

I already have them sorted by A-Z (using the UCase$(Application.Sheets( y).Name) > UCase$(Application.Sheets(y + 1).Name) code below.

Once I have some valid code for sorting tabs by last character, I'll add it under the IF SortOrder = 2 branch shown below.

Here is the current code I have which works for sorting by last name (A-Z):

VBA Code:
Sub AlphabetizeTabs()
    Dim SortOrder As Integer
 
    SortOrder = showUserForm
 
    If SortOrder = 0 Then Exit Sub
    If SortOrder = 2 Then '  Reserved for sorting by GRADE (last character of TAB name)
        Load UnderConstructionForm
        
        ' Sets the position of the UserForm to the middle of the active monitor
        With UnderConstructionForm
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        '.Show
        End With
        
        UnderConstructionForm.Show (1)
        Unload UnderConstructionForm
        Exit Sub
        End If
        
    
    Application.ScreenUpdating = False
    
    For x = 1 To Application.Sheets.Count   ' Counts the total number of sheets in the workbook
        ' *** Set y to 7 to offset the first 6 (Admin) tabs which we want to stay at the beginning of the workbook ***
        For y = 7 To Application.Sheets.Count - 1      ' changed "y=1" to "y=7" in this line
            If SortOrder = 1 Then
                If UCase$(Application.Sheets(y).Name) > UCase$(Application.Sheets(y + 1).Name) Then
                    Sheets(y).Move after:=Sheets(y + 1)
                End If
            ElseIf SortOrder = 2 Then
                '*** This code used to sort tabs by GRADE **** Last character of TAB name
                '*** Place new code here ****
                '*** This code used to sort tabs by GRADE **** Last character of TAB name
                               
            End If
        Next
    Next
    
    'Updates the contents of Instructions Sheet
    Sheets("Instructions").Activate
    
    Application.ScreenUpdating = True
    
End Sub

Thanks folks for your assistance!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the confirmation. :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,143,619
Messages
5,719,762
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top