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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
I think this ought to do it! (Make sure to create the temp sheet called tempSheet before running the TOP sub . . . and save your work before running the code, of course.)

(Put this in a new standard code module.)

VBA Code:
Option Explicit

Sub Reorder_Sheet_Tabs_By_Name_After_Chats_Tab()

'-------------------------------------------------------------------------
'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("A" & 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.
'---------------------------------------------------------------------------
    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

End Sub


Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(sht.Name, " ", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function


Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("220921")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 3, 4) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 5, 6)
End Function


Sub Test__SubString()
MsgBox SubString("ABCDEF", 3, 5)
End Sub
Function SubString(inputString As String, Start As Integer, Finish As Integer)
On Error GoTo Quit
SubString = Mid(inputString, Start, Finish - Start + 1)
Quit:
End Function
 
Last edited:

StillUnderstanding

Board Regular
Joined
Jan 30, 2021
Messages
57
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I think this ought to do it! (Make sure to create the temp sheet called tempSheet before running the TOP sub . . . and save your work before running the code, of course.)

(Put this in a new standard code module.)

VBA Code:
Option Explicit

Sub Reorder_Sheet_Tabs_By_Name_After_Chats_Tab()

'-------------------------------------------------------------------------
'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("A" & 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.
'---------------------------------------------------------------------------
    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

End Sub


Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(sht.Name, " ", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function


Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("220921")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 3, 4) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 5, 6)
End Function


Sub Test__SubString()
MsgBox SubString("ABCDEF", 3, 5)
End Sub
Function SubString(inputString As String, Start As Integer, Finish As Integer)
On Error GoTo Quit
SubString = Mid(inputString, Start, Finish - Start + 1)
Quit:
End Function
Thanks @cmowla for this. I have added 2 tabs tempSheet and tempSheetName and its not working, the macro stops at this hit here:-

Sheets(tempSheetName).Range( _
Sheets(tempSheetName).Range("A1"), _
Sheets(tempSheetName).Range("B" & numberOfDateSheetsToReorder) _
).Sort Key1:=Sheets(tempSheetName).Range("A" & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers

My spreadsheet has a cell that has a date in it but its coming from the tab name using this code:- =IFERROR(DATEVALUE(MID(CELL("filename",E3),FIND("]",CELL("filename",E3))+1,256)),"Template Line")

Any suggestions on how to get it to work?


Thanks
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
I have added 2 tabs tempSheet and tempSheetName and its not working, the macro stops at this hit here:-
You don't need to add tempSheetName. Just tempSheet. (tempSheetName is the name of a variable in the code . . . but adding tempSheetName doesn't hurt anything.)

Checklist. If you:
  1. Start with a new Workbook
  2. Put the code in a new module
  3. Rename the "Sheet1" Worksheet to "tempSheet" (without quotes)
  4. Create a new Worksheet and name it "Chats"
The error you mentioned will come. It's because it doesn't have any sheets that just consist of 6 numbers. (Like your 220921 . . . which I understood to represent September 22, 2021.)

So in order to not get the error, you need to have at least one Worksheet whose name is comprised of 6 digits.

So if you create, say, three additional sheets named 220821, 200921, and 220921, for example, then the program will run without the error. The SORT has nothing to sort (the program only searches for and sorts 6 number Worksheet names), so it gives an error.



But I did notice that I should have had
VBA Code:
    ).Sort Key1:=Sheets(tempSheetName).Range("B" & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers

instead of
VBA Code:
    ).Sort Key1:=Sheets(tempSheetName).Range("A" & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers

because the sort sorts by the dates (which I have in column B of tempSheet).

In addition, I have added error trapping lines of code to exit. Below is the updated code. (Simply replace all previous code of mine with it.)
VBA Code:
Option Explicit

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.
'---------------------------------------------------------------------------
    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

Exit Sub
No_Date_Sheets_To_Sort:
MsgBox "No dated sheets to sort.", vbCritical, "Tab Sorter Failed."

End Sub


Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(sht.Name, " ", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function


Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("220921")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 3, 4) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 5, 6)
End Function


Sub Test__SubString()
MsgBox SubString("ABCDEF", 3, 5)
End Sub
Function SubString(inputString As String, Start As Integer, Finish As Integer)
On Error GoTo Quit
SubString = Mid(inputString, Start, Finish - Start + 1)
Quit:
End Function

When you run the code, it should sort the three example dated sheet tab names in this order, 220821, 200921, and 220921. You can change the order of the sheets (including putting one of them in front of Chats to see that it sorts them in this order and places them immediately after wherever Chats is.)

Technically, you shouldn't have to start with a new Workbook, but this was just an example. You just need the code, along with a tab named Chats and one or more tabs whose name is 6 digits.

Let me know if this was what you were asking for, thanks!
 
Last edited:

StillUnderstanding

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

ADVERTISEMENT

You don't need to add tempSheetName. Just tempSheet. (tempSheetName is the name of a variable in the code . . . but adding tempSheetName doesn't hurt anything.)

Checklist. If you:
  1. Start with a new Workbook
  2. Put the code in a new module
  3. Rename the "Sheet1" Worksheet to "tempSheet" (without quotes)
  4. Create a new Worksheet and name it "Chats"
The error you mentioned will come. It's because it doesn't have any sheets that just consist of 6 numbers. (Like your 220921 . . . which I understood to represent September 22, 2021.)

So in order to not get the error, you need to have at least one Worksheet whose name is comprised of 6 digits.

So if you create, say, three additional sheets named 220821, 200921, and 220921, for example, then the program will run without the error. The SORT has nothing to sort (the program only searches for and sorts 6 number Worksheet names), so it gives an error.



But I did notice that I should have had
VBA Code:
    ).Sort Key1:=Sheets(tempSheetName).Range("B" & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers

instead of
VBA Code:
    ).Sort Key1:=Sheets(tempSheetName).Range("A" & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers

because the sort sorts by the dates (which I have in column B of tempSheet).

In addition, I have added error trapping lines of code to exit. Below is the updated code. (Simply replace all previous code of mine with it.)
VBA Code:
Option Explicit

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.
'---------------------------------------------------------------------------
    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

Exit Sub
No_Date_Sheets_To_Sort:
MsgBox "No dated sheets to sort.", vbCritical, "Tab Sorter Failed."

End Sub


Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(sht.Name, " ", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function


Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("220921")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 3, 4) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 5, 6)
End Function


Sub Test__SubString()
MsgBox SubString("ABCDEF", 3, 5)
End Sub
Function SubString(inputString As String, Start As Integer, Finish As Integer)
On Error GoTo Quit
SubString = Mid(inputString, Start, Finish - Start + 1)
Quit:
End Function

When you run the code, it should sort the three example dated sheet tab names in this order, 220821, 200921, and 220921. You can change the order of the sheets (including putting one of them in front of Chats to see that it sorts them in this order and places them immediately after wherever Chats is.)

Technically, you shouldn't have to start with a new Workbook, but this was just an example. You just need the code, along with a tab named Chats and one or more tabs whose name is 6 digits.

Let me know if this was what you were asking for, thanks!
@cmowla Thank you this is fantastic! I got it to work but then realised that my tabs have a dash in then so 22-08-21 and when I try to run it like that it wont work. Do you know how I would work round that?

One last thing, is it possible to have tabs with past dates sitting after a specific tab, so once the date passes it moved?
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
@cmowla Thank you this is fantastic! I got it to work but then realised that my tabs have a dash in then so 22-08-21 and when I try to run it like that it wont work. Do you know how I would work round that?
No need to work around it. You just displayed them without the dashes, so I couldn't guess that that wasn't your desired form of input. Just replace this function, and it should work just fine.
VBA Code:
Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("22-09-21")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 4, 5) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 7, 8)
End Function

EDIT: Maybe not so. I'm working on it!

Yeah, replace this Function too, then it works!
VBA Code:
Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(Replace(sht.Name, " ", ""), "-", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function

One last thing, is it possible to have tabs with past dates sitting after a specific tab, so once the date passes it moved?
Yes it is. I know this is a stupid question, but when you say "past dates", you mean light yesterday and older, correct?
 

StillUnderstanding

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

ADVERTISEMENT

No need to work around it. You just displayed them without the dashes, so I couldn't guess that that wasn't your desired form of input. Just replace this function, and it should work just fine.
VBA Code:
Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("22-09-21")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 4, 5) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 7, 8)
End Function

EDIT: Maybe not so. I'm working on it!

Yeah, replace this Function too, then it works!
VBA Code:
Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(Replace(sht.Name, " ", ""), "-", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function


Yes it is. I know this is a stupid question, but when you say "past dates", you mean light yesterday and older, correct?
@cmowla the above all works amazing!

Yes so if I have a date that is not today or in the future then it should move to after a tab called "The Past"
If a date is in the future then it should be after a tab called "Chats"
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
Alright, here's the updated (full) code: Does that meet your requirements?
VBA Code:
Option Explicit


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.
        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.
            Exit Sub
        End If
        i = i + 1
    Loop


Exit Sub
No_Date_Sheets_To_Sort:
MsgBox "No dated sheets to sort.", vbCritical, "Tab Sorter Failed."

End Sub



Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(Replace(sht.Name, " ", ""), "-", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function


Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("22-09-21")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 4, 5) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 7, 8)
End Function


Sub Test__SubString()
MsgBox SubString("ABCDEF", 3, 5)
End Sub
Function SubString(inputString As String, Start As Integer, Finish As Integer)
On Error GoTo Quit
SubString = Mid(inputString, Start, Finish - Start + 1)
Quit:
End Function
 
Solution

StillUnderstanding

Board Regular
Joined
Jan 30, 2021
Messages
57
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Alright, here's the updated (full) code: Does that meet your requirements?
VBA Code:
Option Explicit


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.
        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.
            Exit Sub
        End If
        i = i + 1
    Loop


Exit Sub
No_Date_Sheets_To_Sort:
MsgBox "No dated sheets to sort.", vbCritical, "Tab Sorter Failed."

End Sub



Sub Test__Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number()
MsgBox Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number
End Sub
Function Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number(tempSheetName As String)

Dim sht As Worksheet
Dim currentVisibleSheetName As String
Dim currentVisibleSheetNumber As Long

Sheets(tempSheetName).Range("A:B").Value = ""
Sheets(tempSheetName).Range("A:A").NumberFormat = "@"
Sheets(tempSheetName).Range("B:B").NumberFormat = "mm/dd/yy"

currentVisibleSheetNumber = 0
For Each sht In ThisWorkbook.Sheets
    If (Sheets(sht.Name).Visible = -1) And (IsNumeric(Replace(Replace(sht.Name, " ", ""), "-", "")) = True) Then
        currentVisibleSheetNumber = currentVisibleSheetNumber + 1
        currentVisibleSheetName = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 1).Value = sht.Name
        Sheets(tempSheetName).Cells(currentVisibleSheetNumber, 2).Value = Convert_To_Date(sht.Name)
    End If
Next

Put_All_Workbook_Tab_Names_In_Helper_Sheet_And_Return_Last_Row_Number = currentVisibleSheetNumber

End Function


Sub Test__Convert_To_Date()
MsgBox Convert_To_Date("22-09-21")
End Sub
Function Convert_To_Date(tabName As String)
Convert_To_Date = SubString(tabName, 4, 5) & "/" & SubString(tabName, 1, 2) & "/" & SubString(tabName, 7, 8)
End Function


Sub Test__SubString()
MsgBox SubString("ABCDEF", 3, 5)
End Sub
Function SubString(inputString As String, Start As Integer, Finish As Integer)
On Error GoTo Quit
SubString = Mid(inputString, Start, Finish - Start + 1)
Quit:
End Function
@cmowla Amazing! this works fantastic and have solved me issue!

I added this to the top of the code to allow me to reorder the names tabs

VBA Code:
Sheets("Admin").Move before:=Sheets(1)
Sheets("Teams").Move before:=Sheets(2)
Sheets("Chats").Move before:=Sheets(3)
Sheets("Past").Move before:=Sheets(4)

Thank you again, you are a genius and so fast!
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
Thank you again, you are a genius and so fast!
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.
 

Forum statistics

Threads
1,148,362
Messages
5,746,263
Members
424,001
Latest member
Sophiemakescakes

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