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."

Oct 16, 2022 in Others 429 views

## 1 answer to this question.

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`
• 63,700 points

## Is domain name really needed to host a website on the internet?

You do not really need a domain. ...READ MORE

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

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

## 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

## Repeated excel rows based on a cell with multiple values

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

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

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

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

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

## Sort numeric values VBA

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

## Runtime Error when trying to Loop Worksheets

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