Sort Sheet Tabs Named in Date (D.M.YYYY)

[ayman_salah]

I have a workbook and the first sheet on it is named "Home" and after that I have some sheets with the names of dates( for example 21.7.2022, 1.8.2022, .......etc) and I want automatically the excel to sort them descending (1.8.2022, 21.7.2022, ....). except the first sheet throw autorun macro. Thank you very much.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want. Test with a copy of your workbook.

Sub SortSheets()
  Dim AL As Object
  Dim i As Long
  Dim DateBits As Variant
  
  Application.ScreenUpdating = False
  Set AL = CreateObject("System.Collections.ArrayList")
  For i = 2 To Sheets.Count
    DateBits = Split(Sheets(i).Name, ".")
    AL.Add DateBits(2) & Format(DateBits(1), "00") & Format(DateBits(0), "00") & Sheets(i).Name
  Next i
  AL.Sort
  For i = AL.Count - 1 To 0 Step -1
    Sheets(Mid(AL.Item(i), 9)).Move After:=Sheets(Sheets.Count)
  Next i
  Application.ScreenUpdating = True
End Sub

 

[ayman_salah]

Welcome to the MrExcel board!

See if this does what you want. Test with a copy of your workbook.

Sub SortSheets()
  Dim AL As Object
  Dim i As Long
  Dim DateBits As Variant
 
  Application.ScreenUpdating = False
  Set AL = CreateObject("System.Collections.ArrayList")
  For i = 2 To Sheets.Count
    DateBits = Split(Sheets(i).Name, ".")
    AL.Add DateBits(2) & Format(DateBits(1), "00") & Format(DateBits(0), "00") & Sheets(i).Name
  Next i
  AL.Sort
  For i = AL.Count - 1 To 0 Step -1
    Sheets(Mid(AL.Item(i), 9)).Move After:=Sheets(Sheets.Count)
  Next i
  Application.ScreenUpdating = True
End Sub

The code is working fine thanks, Please can you explain step by step how the Code is working

 

[Peter_SSs]

The code puts all the sheet names into an "ArrayList" in the format of "yyyymmdd" followed by the actual sheet name. So 17.5.2022 goes in as "2022051717.5.2022"
5.6.2022 goes in as "202206055.6.2022" etc
The list is then sorted which puts them all in date order due to the structure of the blue parts.
Then work through the sorted list from back to front and cut off the first 8 characters (blue) which returns back to the original name (red) to place each one at the end leaving them in the order you want

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Sort worksheets labelled in dd.mm.yyyy format

I have several worksheets labelled in dd.mm.yyyy format and this is an ever expanding project, with macros creating new sheets in the distant future, but the creation of these sheets puts the order out of sync. Is there any way to sort worksheets descending that have this format dd.mm.yyyy,

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ayman_salah]

Ok, I will do it in the next posts