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:
- Start with a new Workbook
- Put the code in a new module
- Rename the "Sheet1" Worksheet to "tempSheet" (without quotes)
- 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!
