Sorting sheets in a workbook

[brianfosterblack]

I am using this macro to sort my sheets into alphabetical order in my workbook

Sub SortWorksheets()

Dim sCount As Integer, i As Integer, j As Integer
    Application.ScreenUpdating = False
    sCount = Worksheets.Count
    If sCount = 1 Then Exit Sub
    For i = 1 To sCount - 1
        For j = i + 1 To sCount
            If Worksheets(j).Name < Worksheets(i).Name Then
                Worksheets(j).Move Before:=Worksheets(i)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

However I want to still keep my "Master" Sheet as the 1st sheet in the workbook
The "Summary" sheet as the second last sheet and
The "Invoice" sheet as the last sheet.
Is there a way to do this?

 

[Fluff]

How about

Sub SortWorksheets()

Dim sCount As Integer, i As Integer, j As Integer
    Application.ScreenUpdating = False
    sCount = Worksheets.Count
    If sCount = 1 Then Exit Sub
    For i = 2 To sCount - 3
        For j = i + 1 To sCount - 2
            If Worksheets(j).Name < Worksheets(i).Name Then
                Worksheets(j).Move Before:=Worksheets(i)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

 

[brianfosterblack]

How about

Sub SortWorksheets()

Dim sCount As Integer, i As Integer, j As Integer
    Application.ScreenUpdating = False
    sCount = Worksheets.Count
    If sCount = 1 Then Exit Sub
    For i = 2 To sCount - 3
        For j = i + 1 To sCount - 2
            If Worksheets(j).Name < Worksheets(i).Name Then
                Worksheets(j).Move Before:=Worksheets(i)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

Hi Fluff, I am using this code after adding a worksheet to the workbook. In this Case the worksheet may be added as the last sheet or the first sheet and in that case the code above will not work.

 

[Fluff]

In that case just use your code & add a bit at the end to move the other sheets to where you want them.

 

[brianfosterblack]

Thanks fluff, I geuse I am looking for the code then to do just that.

 

[Fluff]

How about

    Sheets("Master").Move Sheets(1)
    Sheets("Summary").Move , Sheets(Sheets.Count)
    Sheets("Invoice").Move , Sheets(Sheets.Count)

 

[brianfosterblack]

How about

    Sheets("Master").Move Sheets(1)
    Sheets("Summary").Move , Sheets(Sheets.Count)
    Sheets("Invoice").Move , Sheets(Sheets.Count)

Hi Fluff, this works perfectly except for the Master Sheet. The Master sheet is sheet1 but sometimes when I add a sheet like with the name 1 or using lowercase letters in the name, I am getting those in the workbook being inserted before sheet 1.

 

[Fluff]

You need to put those 3 lines after the loop in your code.

 

[brianfosterblack]

Hi Fluff, thanks again for your help. I was calling the macro from the worksheet_Activate module but the moment I moved it into a separate module it works fine
Thanks again for the patience.

 

[Fluff]

You're welcome & thanks for the feedback.