VBA to sort Sheets in descending order

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
77
Office Version
  1. 365
Wondering if there is some VBA code that may look at sheets 2 through (sheet count), and sort them in descending order as shown?

I have a process that compares data between each sheet, and they must be in proper order. I do not trust the users to make sure these sheets are sorted correctly :)
Thanks!

Below is an example of the format in my workbook.
1618867241504.png
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
How about this?

VBA Code:
Sub SortSheets()
Application.ScreenUpdating = False
Dim wb As Workbook: Set wb = ThisWorkbook

For i = 2 To wb.Sheets.Count
    For j = i + 1 To wb.Sheets.Count
        If wb.Sheets(j).Name > wb.Sheets(i).Name Then wb.Sheets(j).Move before:=wb.Sheets(i)
    Next j
Next i
Application.ScreenUpdating = True
End Sub
 
Last edited:

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
This code will add a worksheet and sortthe naes in descendiong order:
VBA Code:
Sub test4()
Dim wksnames()
wno = Worksheets.Count
ReDim whsnames(1 To Worksheets.Count, 1 To 1)
For i = 1 To wno
 whsnames(i, 1) = Worksheets(i).Name
Next i
Worksheets.Add
Range(Cells(1, 1), Cells(wno, 1)) = whsnames
    Set myrange = Range(Cells(1, 1), Cells(wno, 1))
    Set Sortkey = Range(Cells(1, 1), Cells(wno, 1))
    myrange.Sort key1:=Sortkey, order1:=xlDescending, MatchCase:=False, Header:=xlNo
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
Another way only for sheets named as numbers :​
VBA Code:
Sub Demo1()
    With Sheets
        If .Count > 2 Then
            For N& = 3 To .Count:  S$ = S$ & "," & .Item(N).Name:  Next
            V = Evaluate("TRANSPOSE(LARGE({" & .Item(2).Name & S & "},ROW(1:" & .Count - 1 & ")))")
            For N = 1 To .Count - 2:  .Item(CStr(V(N))).Move .Item(N + 1):  Next
        End If
    End With
End Sub
 
Last edited:

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

My previous demonstration revamped in order to move sheets only when necessary and to keep the active sheet as the active one :​
VBA Code:
Sub Demo1r()
    With Sheets
        If .Count > 2 Then
                For N& = 3 To .Count:  S$ = S$ & "," & .Item(N).Name:  Next
                V = Evaluate("TRANSPOSE(LARGE({" & .Item(2).Name & S & "},ROW(1:" & .Count - 1 & ")))")
                S = ActiveSheet.Name
            For N = 1 To .Count - 2
                If .Item(N + 1).Name * 1 <> V(N) Then .Item(CStr(V(N))).Move .Item(N + 1)
            Next
                Sheets(S).Select
        End If
    End With
End Sub
 

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
77
Office Version
  1. 365
Thank you all for the replies.

I guess the one curve ball that i didn't mention in my OP was that the tab names are actually dates.

So while 120220 might be greater than 030221 as a number, it would actually appear after 030221 in my use case. So looks like I have two options, what do you all think is the best?

1. Somehow convert the sheet names to a date
2. Format the dates on the tabs with year first ie: Dec 21 2020 as 201220
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

As important information must be in the initial post …​
Confirm at least your worksheet name actual date format as mm/dd/yy the same as your Windows Locals Settings ?​
 
Last edited:

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
77
Office Version
  1. 365
@Marc L my apologies.

So the sheets in the OP are mmddyy

Yes that is the same as my windows local settings. I could easily name them as yymmdd to make things easier if that is likely the best route.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
First I will try to keep the original format as it is - so mmddyy - just playing with Excel basics …​
 

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
77
Office Version
  1. 365
Thanks.. problem there is with the VBA above, it does not recognize the numbers as dates. Any suggestions on how to do that?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,577
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top