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 in Others by Kithuzzz
• 20,660 points
51 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 by narikkadan
• 37,660 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
43 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 in Others by narikkadan
• 37,660 points
61 views
0 votes
1 answer

Excel Define a range based on a cell value

Let's say that cells A1, A2, A3, ...READ MORE

answered Nov 8 in Others by narikkadan
• 37,660 points
46 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,720 points
454 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,227 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 in Others by gaurav
• 22,040 points
71 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 in Others by Edureka
• 13,640 points
210 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 in Others by narikkadan
• 37,660 points
91 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 in Others by narikkadan
• 37,660 points
46 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