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
• 38,020 points
519 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
• 63,720 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
343 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
• 63,720 points
1,340 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
• 63,720 points
1,461 views
0 votes
1 answer

Having issues with pop-up alert in excel. (Visual Basic)

You may find the following code of ...READ MORE

answered Feb 4, 2023 in Others by narikkadan
• 63,720 points
606 views
0 votes
1 answer

How can I stop my vba code from giving me an error 424?

Object Variables in Loops The main issue was ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,720 points
853 views
0 votes
1 answer

Sort numeric values VBA

Try: The formula in B1: =SORTBY(A1:A8,LEFT(A1:A8&"0000 ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,020 points
411 views
0 votes
1 answer

Runtime Error when trying to Loop Worksheets

Copy To Multiple Worksheets A Quick Fix Option Explicit Sub ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,020 points
364 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
• 63,720 points
5,336 views
0 votes
1 answer

Is there a way to produce a sum according to date/time stamp values of another column on excel?

Assuming the input data for the second ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,720 points
565 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