Reorder tabs in excel based on past dates

StillUnderstanding

Board Regular
Joined
Jan 30, 2021
Messages
57
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I wonder if anyone can help me please.

I have a spreadsheet that has 50 tabs on it, the tab names are things like Admin and 220921 and what I am wanting to do is set the order of the text named tabs but with the date names tabs I want them in order of Date.

So for dates to happen they should be after the "Chats" tab and when the date is passed the tab should be after the "Past" tab.

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Sheets("Admin").Move before:=Sheets(1)
        Sheets("Teams").Move before:=Sheets(2)
        Sheets("Chats").Move before:=Sheets(3)
        Sheets("Past").Move before:=Sheets(4)
        
End Sub

It would be really great if someone could help me with this.

Thank you!
 

StillUnderstanding

Board Regular
Joined
Jan 30, 2021
Messages
57
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You're quite welcome!

I maybe fast in making changes because of my style of programming. (Breaking things into a bunch of smaller subs and functions.) Just having a library of hundreds of functions, where each does just one thing, really helps to debug and update/maintain more complicated systems.
@cmowla Just using the macro and notice that once completed it sends me to the very last tab on the sheet as the active tab. I tried to set it to the "Admin" tab as being the active tab but it wont do it.

Would you be able to help once again?

Thanks
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
@cmowla Just using the macro and notice that once completed it sends me to the very last tab on the sheet as the active tab. I tried to set it to the "Admin" tab as being the active tab but it wont do it.

Replace Sub Reorder_Sheet_Tabs_By_Name_After_Chats_Tab() with the following:
VBA Code:
Sub Reorder_Sheet_Tabs_By_Name_After_Chats_Tab()

On Error GoTo No_Date_Sheets_To_Sort

'-------------------------------------------------------------------------
'Put the sheet tab names which are in the form of "dates" in a temp sheet.
'-------------------------------------------------------------------------
    '(You must manually create this tempSheet once, but it does not need to be deleted and remade every time.)
    Dim tempSheetName As String
    tempSheetName = "tempSheet"
 
    Dim numberOfDateSheetsToReorder As Integer
    numberOfDateSheetsToReorder = Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName)

'------------------------
'Sort the "table" by date
'------------------------
    'DataOption1:=xlSortTextAsNumbers will not affect sorting of alphabetical letters.  Just when it's numbers, it will sort it CORRECTLY.
    '"A1" is the topLeftCornerAddress
    '"B" & currentVisibleSheetNumber is the bottomRightCornerAddress.
    Sheets(tempSheetName).Range( _
    Sheets(tempSheetName).Range("A1"), _
    Sheets(tempSheetName).Range("B" & numberOfDateSheetsToReorder) _
    ).Sort Key1:=Sheets(tempSheetName).Range("B" & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers

'--------------------------------------------
'Move the earliest dated sheet after "Chats".
'--------------------------------------------
    Dim currentSheetName As String
    currentSheetName = Sheets(tempSheetName).Cells(1, 1).Value
 
    Dim sht As Worksheet
    Set sht = Sheets(currentSheetName)
    sht.Move after:=Sheets("Chats")
 
Dim previousSheetName As String
previousSheetName = Sheets(tempSheetName).Cells(1, 1).Value

'---------------------------------------------------------------------------
'Now in a loop, move the remaining sheets after each other in order by date. (After "Chats")
'---------------------------------------------------------------------------
    Dim i As Integer
    i = 2
    Do While i <= numberOfDateSheetsToReorder
        currentSheetName = Sheets(tempSheetName).Cells(i, 1).Value
        Set sht = Sheets(currentSheetName)
        sht.Move after:=Sheets(previousSheetName)
        previousSheetName = Sheets(tempSheetName).Cells(i, 1).Value
        i = i + 1
    Loop

'--------------------------------------------------
'Move the earliest past date sheet after "The Past"
'--------------------------------------------------
    If Sheets(tempSheetName).Cells(1, 2).Value - Date < 0 Then
        currentSheetName = Sheets(tempSheetName).Cells(1, 1).Value
 
        Set sht = Sheets(currentSheetName)
        sht.Move after:=Sheets("The Past")

        previousSheetName = Sheets(tempSheetName).Cells(1, 1).Value
    Else
        'There are no past dates.  So exit.
        GoTo Exit_Sub
    End If
'---------------------------------------------------------------------------
'Now in a loop, move the remaining past date sheets after each other in order by date.  (After "The Past")
'---------------------------------------------------------------------------

    i = 2
    Do While i <= numberOfDateSheetsToReorder
        If Sheets(tempSheetName).Cells(i, 2).Value - Date < 0 Then
            currentSheetName = Sheets(tempSheetName).Cells(i, 1).Value
            Set sht = Sheets(currentSheetName)
            sht.Move after:=Sheets(previousSheetName)
            previousSheetName = Sheets(tempSheetName).Cells(i, 1).Value
        Else
            'There are no more past dates.  Exit.
            GoTo Exit_Sub
        End If
        i = i + 1
    Loop


GoTo Exit_Sub
No_Date_Sheets_To_Sort:
MsgBox "No dated sheets to sort.", vbCritical, "Tab Sorter Failed."

Exit_Sub:
Sheets("Admin").Select

End Sub
 
Last edited:

StillUnderstanding

Board Regular
Joined
Jan 30, 2021
Messages
57
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Replace Sub Reorder_Sheet_Tabs_By_Name_After_Chats_Tab() with the following:
VBA Code:
Sub Reorder_Sheet_Tabs_By_Name_After_Chats_Tab()

On Error GoTo No_Date_Sheets_To_Sort

'-------------------------------------------------------------------------
'Put the sheet tab names which are in the form of "dates" in a temp sheet.
'-------------------------------------------------------------------------
    '(You must manually create this tempSheet once, but it does not need to be deleted and remade every time.)
    Dim tempSheetName As String
    tempSheetName = "tempSheet"
 
    Dim numberOfDateSheetsToReorder As Integer
    numberOfDateSheetsToReorder = Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName)

'------------------------
'Sort the "table" by date
'------------------------
    'DataOption1:=xlSortTextAsNumbers will not affect sorting of alphabetical letters.  Just when it's numbers, it will sort it CORRECTLY.
    '"A1" is the topLeftCornerAddress
    '"B" & currentVisibleSheetNumber is the bottomRightCornerAddress.
    Sheets(tempSheetName).Range( _
    Sheets(tempSheetName).Range("A1"), _
    Sheets(tempSheetName).Range("B" & numberOfDateSheetsToReorder) _
    ).Sort Key1:=Sheets(tempSheetName).Range("B" & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers

'--------------------------------------------
'Move the earliest dated sheet after "Chats".
'--------------------------------------------
    Dim currentSheetName As String
    currentSheetName = Sheets(tempSheetName).Cells(1, 1).Value
 
    Dim sht As Worksheet
    Set sht = Sheets(currentSheetName)
    sht.Move after:=Sheets("Chats")
 
Dim previousSheetName As String
previousSheetName = Sheets(tempSheetName).Cells(1, 1).Value

'---------------------------------------------------------------------------
'Now in a loop, move the remaining sheets after each other in order by date. (After "Chats")
'---------------------------------------------------------------------------
    Dim i As Integer
    i = 2
    Do While i <= numberOfDateSheetsToReorder
        currentSheetName = Sheets(tempSheetName).Cells(i, 1).Value
        Set sht = Sheets(currentSheetName)
        sht.Move after:=Sheets(previousSheetName)
        previousSheetName = Sheets(tempSheetName).Cells(i, 1).Value
        i = i + 1
    Loop

'--------------------------------------------------
'Move the earliest past date sheet after "The Past"
'--------------------------------------------------
    If Sheets(tempSheetName).Cells(1, 2).Value - Date < 0 Then
        currentSheetName = Sheets(tempSheetName).Cells(1, 1).Value
 
        Set sht = Sheets(currentSheetName)
        sht.Move after:=Sheets("The Past")

        previousSheetName = Sheets(tempSheetName).Cells(1, 1).Value
    Else
        'There are no past dates.  So exit.
        GoTo Exit_Sub
    End If
'---------------------------------------------------------------------------
'Now in a loop, move the remaining past date sheets after each other in order by date.  (After "The Past")
'---------------------------------------------------------------------------

    i = 2
    Do While i <= numberOfDateSheetsToReorder
        If Sheets(tempSheetName).Cells(i, 2).Value - Date < 0 Then
            currentSheetName = Sheets(tempSheetName).Cells(i, 1).Value
            Set sht = Sheets(currentSheetName)
            sht.Move after:=Sheets(previousSheetName)
            previousSheetName = Sheets(tempSheetName).Cells(i, 1).Value
        Else
            'There are no more past dates.  Exit.
            GoTo Exit_Sub
        End If
        i = i + 1
    Loop


GoTo Exit_Sub
No_Date_Sheets_To_Sort:
MsgBox "No dated sheets to sort.", vbCritical, "Tab Sorter Failed."

Exit_Sub:
Sheets("Admin").Select

End Sub
Thanks for this. It works a treat!!!!
 

Forum statistics

Threads
1,148,370
Messages
5,746,303
Members
424,006
Latest member
Metal_warrior

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