Sorting sheets

[Damo10]

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

 

[JackDanIce]

That is do-able but can you post an example of what the other sheets are called that you want sorted?

 

[Damo10]

Hi,

Most are department numbers

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

Hope that helps

Regards

 

[JackDanIce]

So what would the order be?

Main, Summary, Report
Then the number ones
Then the none number ones?

 

[Damo10]

yes that would be the order I would like

 

[JackDanIce]

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.

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

 

[shg]

Another way:

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

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,

SortSheets ActiveWorkbook, 4

 

[JackDanIce]

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

 

[Damo10]

Thankyou both for your replys, they both work great

Regards

 

[shg]

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