Sort Excel worksheets based on name which is a date

0 votes
I have an Excel workbook with some macros. The current date can be used as the worksheet's name, or the user can manually enter a date and the worksheet will be produced.

The problem is that the worksheet comprises two sheets, Initial and Version, which need to be used in that order. Every time a new worksheet is created, all worksheets made in between should be sorted by date. For example, I may have names like "1-11-21," "2-11-21," "11-11-21," and "21-11-21" in the same worksheet and it should be ordered ascending. The sheets are "DD-MM-YY."

Can someone please help me with this?
Oct 16, 2022 in Others by Kithuzzz
• 27,940 points
96 views

1 answer to this question.

0 votes

Sorting sheets of a workbook are rather easy, there a numerous examples out there, looking more or less like this:

Sub SortSheets(Optional wb As Workbook = Nothing)
    If wb Is Nothing Then Set wb = ActiveWorkbook  ' (or maybe ThisWorkbook)
    
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    
    For i = 1 To wb.Worksheets.Count - 1
        For j = i + 1 To wb.Worksheets.Count
            ' ==> The following line needs to be replaced!
            If wb.Worksheets(j).Name < wb.Worksheets(i).Name Then
                wb.Worksheets(j).Move before:=wb.Worksheets(i)
            End If
        Next j
    Next i
    ' Application.ScreenUpdating = True
End Sub

Now, only the If-statement needs to be altered in the logic. You must locate a custom logic that compares the names of the two sheets rather than simply comparing the names of the sheets.

Basically, your reasoning goes like this: If the name is Initial, put it at the top; if it's Version, put it at the bottom; and for all the rest, put them in order of the date the name represents.

I developed a tiny function that derives a value from the name. The Initial sheets receive a value of 0, the Version sheets receive an arbitrarily high number, and a worksheet with a date in the name receives the date value by transforming the name into the date (a date in VBA is essentially a double value). If the name cannot be converted to a date, the value will be so that the sheet will be sorted to the end (but before the version sheet). 

You can adapt the function easily if your needs changed (eg date format or you can have extra text with the date, or you want to sort the version sheet to the beginning, or you have additional sheets with different names...). The sort function itself will not change at all, only the comparison logic.

Now all you have to do is change the line in the sort routine:

If wb.Worksheets(j).Name < wb.Worksheets(i).Name Then

to

If getSortNumber(wb.Worksheets(j)) < getSortNumber(wb.Worksheets(i)) Then
answered Oct 17, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
1 answer
0 votes
0 answers

get unique distinct items based on a condition and a date condition

hi could you help me sorry my ...READ MORE

Nov 23, 2021 in Others by adolpo
• 120 points
61 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 51,600 points
103 views
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 51,600 points
154 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
523 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,390 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
112 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
284 views
0 votes
1 answer

Is there a limit on an Excel worksheet's name length?

The file format would allow worksheet names ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 51,600 points
82 views
0 votes
1 answer

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

The Excel Web version (which, based on ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,600 points
162 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP