VBA to sort Sheets in descending order

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
85
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
@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.
 
Upvote 0
First I will try to keep the original format as it is - so mmddyy - just playing with Excel basics …​
 
Upvote 0
Thanks.. problem there is with the VBA above, it does not recognize the numbers as dates. Any suggestions on how to do that?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top