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

M

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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution
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!
 
Upvote 0
MarqyMarq 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?
Click to expand...
It should be just this
Rich (BB code): 
For i = 7 To Sheets.Count - 1
For i = 7 To Sheets.Count
 
Upvote 0
Peter_SSs said:
It should be just this
Rich (BB code): 
For i = 7 To Sheets.Count - 1
For i = 7 To Sheets.Count
Click to expand...

Excellent! It works as expected.

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

Thanks again for all your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Multiple Monitors - tab selection
Replies
5
Views
287
Candyman8019
C
C
  • Question Question
Tab Selection from Userform based on Date range
Replies
5
Views
134
campbell1093
C
Guinaba
  • Solved
Looping across all tabs and deleting empty rows
Replies
6
Views
319
Alex Blakenburg
A
N
  • Question Question
Pivot graph data when creating a new tab from a template.
Replies
0
Views
248
Nippyzap
N
E
  • Solved
Custom Sorting VBA
Replies
3
Views
344
eli_m
E

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

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
Back
Top