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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,210
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try (untested)
VBA Code:
If Right(Application.Sheets(y).Name, 1) > Right(Application.Sheets(y + 1).Name, 1) Then
                    Sheets(y).Move after:=Sheets(y + 1)

Btw, the tab name is the sheetname, if you mean the name outside the brackets in the VBE project window then that is the sheets codename.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that you have got to where SortOrder is either 1 or 2, then this should sort either way based on that. That is, alphabetically if Sortorder = 1 and by last character if SortOrder = 2.

VBA Code:
Dim AL As Object
Dim itm As Variant
Dim i As Long

Application.ScreenUpdating = False
Set AL = CreateObject("System.Collections.ArrayList")
For i = 8 To Sheets.Count
  AL.Add IIf(SortOrder = 1, 1, Right(Sheets(i).Name, 1)) & Sheets(i).Name
Next i
AL.Sort
For Each itm In AL.Toarray
  Sheets(Mid(itm, 2)).Move After:=Sheets(Sheets.Count)
Next itm
Application.ScreenUpdating = True
 

MarqyMarq

New Member
Joined
Oct 22, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Mark858... Code worked well! Thank you!
Peter... I'll be testing your solution as well.

Need a little tweak though... the grades (1-6) also include the letter "K" for Kindergarten, but the "K" ends up at the back of the pack, instead of the front.

How can you sort the tabs by a string first (K), then by numbers (1-6)?

I have code embedded in a table where I use a "helper" column, =IF(ISBLANK([@Grade]),"",IF(ISTEXT(E14),0,E14)). The helper column searches field for a TEXT and set it to "0".
Then I do a sort on it, bringing the K to the top of the table.

For the TABS, how do I do the same concept of placing the K in front of the 1-6 grades? Using the same code structure as before.
1628023244520.png


This solution would get it to where we want it!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this modification

Rich (BB code):
Dim AL As Object
Dim itm As Variant
Dim i As Long

Application.ScreenUpdating = False
Set AL = CreateObject("System.Collections.ArrayList")
For i = 8 To Sheets.Count
  AL.Add IIf(SortOrder = 1, 1, Replace(Right(Sheets(i).Name, 1), "K", 0)) & Sheets(i).Name
Next i
AL.Sort
For Each itm In AL.Toarray
  Sheets(Mid(itm, 2)).Move After:=Sheets(Sheets.Count)
Next itm
Application.ScreenUpdating = True
 

MarqyMarq

New Member
Joined
Oct 22, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
To Peter_SSs: I tried your code and it works. ..somewhat. I am stumped again, but this time, for timing reasons. As I added more sheets (in increments of 5), the sorting time of K, 1-6 was getting longer in seconds, compared to near-instantaneous with code A-Z. Not sure why that is the case in sorting only 16 sheets takes over 75 seconds to complete, (separate from the other 7 admin sheets).

I did try Mark858 solution , but his solution sorted the sheets logically, with the "K" sheets at the end, after the numbered sheets (1-6). Remember, we are sorting on the last character of the Tab name (Sheet name), but we need the "K" to come before the grades 1-6.

Please look at the code under Sort Order 2 and see what would be causing the delayed results.

Rich (BB code):
Sub AlphabetizeTabs()
'On Error Resume Next
  
    Dim SortOrder As Integer
 
    SortOrder = showUserForm
 
    If SortOrder = 0 Then Exit Sub
  
    If SortOrder = 3 Then '  Reserved for sorting by SUBJECT (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       '*** This code used to sort tabs by Last Name (A-Z) ****
                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 in Tab name) ****
              
                ' *** This code works - partially, does NOT place "K" before grades 1-6 *** Code from Mark858 (MrExcel MVP) ***
                ' *** Got code from https://www.mrexcel.com/board/threads/sort-tabs-by-last-character-in-tab-name-not-sheet-name.1178038/#post-5731809
             
                'If Right(Application.Sheets(y).Name, 1) > Right(Application.Sheets(y + 1).Name, 1) Then
                '    Sheets(y).Move after:=Sheets(y + 1)
                 '*** This code used to sort tabs by Grade (Last character in Tab name) ****
                'End If
              
              
                ' *** This code works, but takes 75 seconds to sort 16 sheets!   *** Code from Peter_SSe (MrExcel Moderator)
                ' *** Got code from https://www.mrexcel.com/board/threads/sort-tabs-by-last-character-in-tab-name-not-sheet-name.1178038/#post-5731809
                Dim AL As Object
                Dim itm As Variant
                Dim i As Long
                
                Set AL = CreateObject("System.Collections.ArrayList")
                For i = 7 To Sheets.Count
                  ' Sorts by K first, then by 1-6
                  AL.Add IIf(SortOrder = 1, 1, Replace(Right(Sheets(i).Name, 1), "K", 0)) & Sheets(i).Name
                
                Next i
                AL.Sort
                For Each itm In AL.Toarray
                  Sheets(Mid(itm, 2)).Move After:=Sheets(Sheets.Count)
                Next itm
          
            End If
        Next
    Next
  
    'Updates the contents of Instructions Sheet
    Sheets("Instructions").Activate
  
    Application.ScreenUpdating = True
  
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I do not have your userforms to test but with say 16 sheets it looks to me that with SortOder = 2 your are sorting the sheets about 144 times
x = 1 to 16 = 16 loops
y = 7 to 15 = 9 loops
That would explain an extended execution time.

I may have been slightly mistaken about the number and placement of your admin sheets.
Am I right in thinking from your code that you have 6 at the left and one at the right?
.. and if so, do you need/want that one to stay at the right or could/should it be moved by the sorting process to come after the first 6 and before all the sorted ones?

One reason there are so many loops is I think that you have not picked up on my earlier comment (post #3) that my code does both the SortOrder1 and SortOrder2 sorting.
Note the extra comments below in my section of the code

So, assuming 6 admin sheets at the left and one at the right and it should stay there give this a try.
As mentioned above I have not been able to fully test this as I do not have the userforms.

VBA Code:
Sub AlphabetizeTabs()
'On Error Resume Next
  
    Dim SortOrder As Integer
 
    SortOrder = showUserForm
 
    If SortOrder = 0 Then Exit Sub
  
    If SortOrder = 3 Then '  Reserved for sorting by SUBJECT (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
                
    Dim AL As Object
    Dim itm As Variant
    Dim i As Long
    
    Set AL = CreateObject("System.Collections.ArrayList")
    For i = 7 To Sheets.Count - 1
      ' Sorts by K first, then by 1-6
      'OR
      'normal alphabetically
      'depending on the SortOrder value
      AL.Add IIf(SortOrder = 1, 1, Replace(Right(Sheets(i).Name, 1), "K", 0)) & Sheets(i).Name
    Next i
    AL.Sort
    For Each itm In AL.Toarray
      Sheets(Mid(itm, 2)).Move Before:=Sheets(Sheets.Count)
    Next itm
          
    'Updates the contents of Instructions Sheet
    Sheets("Instructions").Activate
  
    Application.ScreenUpdating = True
End Sub
 
Solution

MarqyMarq

New Member
Joined
Oct 22, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
My utmost apology! In reading my post I can see the confusion on the number of admin tabs... there are only 6 admin tabs. All the tabs to the right will be for Student Tabs.
Specifically, I am duplicating the MASTER tab and renaming them as they are created.

As you can see from the pic, BriceLee, Paul-K is now first (after the Admin Tabs), so your code is working as you designed it, both on Sort Order 1 and Sort Order 2!

1628313029960.png


So, with that said, what line of code do I need to change to include all the Student tabs after the 6 (not 7) admin tabs in all the sort options?

I have very little knowledge of arraylists, and I can see the utility in learning this function.

Thanks again Peter_SSs for all your help!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
what line of code do I need to change to include all the Student tabs after the 6 (not 7) admin tabs in all the sort options?
It should be just this
Rich (BB code):
For i = 7 To Sheets.Count - 1
For i = 7 To Sheets.Count
 

MarqyMarq

New Member
Joined
Oct 22, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
It should be just this
Rich (BB code):
For i = 7 To Sheets.Count - 1
For i = 7 To Sheets.Count

Excellent! It works as expected.

I should have seen that! I was thinking it was in the array.

Thanks again for all your help!
 

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