Sort Sheets Alphanumerically

[Deepak Sharma]

Hellos,

I have coded to sort the sheets but it's not sorting sheets in accurate format.

After Sheet1 it should be Sheet2...sheet3...and so on, but it's giving Sheet10 after sheet1.

Please see attached file for code also.


Thanks and Regards,
Deepak Sharma

 

[Chihiro]

Actually it's doing it correctly. Since Sheet1, Sheet2 etc are considered string and when string is sorted alphabetically...
Sheet1, Sheet10, Sheet2... etc.
Since 1 is smaller than 2. Each digit is considered separately in sorting according to their position within the string.

If you need to sort it by sheet number portion. You'd need to separate out number portion and sort based on that. Or add leading zero (1 or 2 as needed) at start of numeric portion.

Ex: Sheet01, Sheet02 ... Sheet10 ... Sheet99.

If more than 99 sheets up to 999, add 2 leading zero.

 

[Deepak Sharma]

Thanks Chihiro for your time.

Actually I have some other code as below but in that when I am adding sheets then it's inserting into left side.....If it would insert sheets on right side, then there would be no issue to sort out the sheets.

Can you please look into this & edit how to do this..

' I am separating every 1500 rows from Sheet1 to multiple sheets with this code :-

Sub test()
Dim lastRow As Long, myRow As Long, mySheet As Worksheet
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For myRow = 2 To lastRow Step 1500
Set mySheet = Worksheets.Add
Sheets("Sheet1").Rows(myRow & ":" & myRow + 1499).EntireRow.Copy mySheet.Range("A2")
Next myRow
End Sub


Regards,

 

[Chihiro]

Change Set line to something like...

Set mySheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))

 

[Deepak Sharma]

Thank you so much Dear...I was not putting Bracket after .add actually, thats why it was showing error.

Set mySheet = Worksheets.Add (After:=Worksheets(Worksheets.Count))

Now it's working fine. Thanks !!!

Regards,