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