Sorting sheets

D

Damo10

Active Member
Joined
Dec 13, 2010
Messages
460
Hi,

I have a workbook with lots of sheets, is it possible to have a macro that will sort the sheets alphanumerically but to keep sheets "Main",Summary" & "Report" as the first ones. I would like to run this after my add new sheet macro.

Regards
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
That is do-able but can you post an example of what the other sheets are called that you want sorted?
 
Upvote 0
Hi,

Most are department numbers

120
121
122 etc
then there are named sheets
HR
Engineering
Managers
etc

Hope that helps

Regards
 
Upvote 0
So what would the order be?

Main, Summary, Report
Then the number ones
Then the none number ones?
 
Upvote 0
It's not the best suggestion but what below does is creates a temporary sheet called "Temp" to store the names of all your worksheets (excluding Main, Report and Summary), sorts those names in ascending order, using the worksheet's sort, moves the sheets to match the order of the names, then deletes the temp sheet it created.
Code: 
Sub ALampWithNoLight()
 
Dim i As Long, j As Long
Dim ws As String
 
With Application
    .ScreenUpdating = False
    .Calculation = xlManual
    .DisplayStatusBar = False
    .DisplayAlerts = False
End With
 
Sheets("Main").Move Before:=Sheets(1)
Sheets("Summary").Move Before:=Sheets(2)
Sheets("Report").Move Before:=Sheets(3)
Sheets.Add.Name = "Test"
 
j = Worksheets.Count
 
With Sheets("Test")
    .Move Before:=Sheets(1)
    For i = 5 To j
        .Range("A" & i - 4) = Sheets(i).Name
    Next i
    .Range("A1:A" & j).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
    For i = 5 To j
        ws = .Range("A" & i - 4)
        Sheets(ws).Move Before:=Sheets(i)
    Next i
    .Delete
End With
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .DisplayStatusBar = True
    .DisplayAlerts = True
End With
 
End Sub
I think a better method would be to avoid creating a temporary sheet, but instead use an array to hold the names of the sheets you want sorted, apply a quick sort to the array elements and then re-order the sheets as per the array, before removing the array.

However, I don't know how to code arrays properly yet and so would take too much time/effort to do for yours. However, I don't believe it would take much additional effort for anyone better versed with arrays and quicksort method to adapt the above.

Anyway, hope code works,
Jack
 
Upvote 0
Another way:

Code: 
Sub SortSheets(Optional wkb As Workbook = Nothing, _
               Optional ByVal iBeg As Long = 1, _
               Optional ByVal iEnd As Long = 32767)
    ' shg 2009-09
    ' Insertion-sorts sheets from iBeg to iEnd
    Dim i           As Long
    Dim j           As Long
 
    If wkb Is Nothing Then Set wkb = ActiveWorkbook
 
    With wkb
        If iBeg < 1 Then iBeg = 1
        If iEnd > .Sheets.Count Then iEnd = .Sheets.Count
 
        For i = iBeg + 1 To iEnd
            For j = iBeg To i - 1
                If .Sheets(i).Name < .Sheets(j).Name Then
                    .Sheets(i).Move Before:=.Sheets(j)
                    Exit For
                End If
            Next j
        Next i
    End With
End Sub

E.g.,

Code: 
SortSheets ActiveWorkbook
Sheets("Report").Move Before:=Sheets(1)
Sheets("Summary").Move Before:=Sheets(1)
Sheets("Main").Move Before:=Sheets(1)

Or, if the first three sheets are already at the front of the workbook,

Code: 
SortSheets ActiveWorkbook, 4
 
Last edited:
Upvote 0
Hi shg,

If I've read your code right, it's sorting the worksheet names recursively using nested FOR loops where iBeg and iEnd are re-initialised; hence without the need of a temporary sheet or array to sort the sheets? Grr I used to know what type of sort method that is too but forgotten!

And that looks a much simpler way to think of the solution; to first sort all the sheet names and then re-arrange "Main", "Report" and "Summary" at the end. As opposed to pull out those ones and then sort the remaining..

Thanks for posting,
Jack
 
Upvote 0
@ Damo: You're welcome. There are always lots of ways to do things, and seeing variations stirs your brain juices.

@ Jack: It's not recursive (it doesn't call itself); an insertion sorts moves one item at a time toward the front of a list, starting with the 2nd item. It works nicely here because, unlike array elements, you can move sheets. If you step through the code and watch the sheet tabs move, you'll get the idea.

A nice thing about insertion sorts (though not applicable for the 1D sort here) is that they are stable, as is Excel's sorting. That means that two records with identical sort fields remain in the same order. That is not the case for QuickSort and lots of others.

For sorting in the UI, stable sorting it means that you can circumvent the three sort-field limit in Excel 2003- by sorting first by the lower priority fields, and then again by the higher-priority fields.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Condition format highlight duplicates between sheets
Replies
3
Views
148
6StringJazzer
6StringJazzer
E
  • Question Question
Write Macro to highlight based on values in another sheet
Replies
3
Views
298
Peter_SSs
Peter_SSs
M
  • Question Question
Reference a value from another sheet that might be in a different place each time
Replies
2
Views
69
M3K1RKJP
M
GMC The Macro Man
  • Question Question
Sorting a table in a protected sheet when some cells are locked -MACRO needed
Replies
3
Views
426
RoryA
RoryA
C
  • Question Question
Summary page with paste links showing 0
Replies
1
Views
35
Alex Blakenburg
A

Forum statistics

Threads
1,224,509
Messages
6,179,192
Members
452,893
Latest member
denay

Share this page

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