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

MarqyMarq

New Member
Joined
Oct 22, 2015
Messages
30
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,757
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the confirmation. :)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,147,820
Messages
5,743,387
Members
423,792
Latest member
travisds

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