Worksheet Sort - I need to keep code named sheets 1, 2 and 3

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
I thought I had already asked this question.

I need to have sheets Sheet1, Sheet2, and Sheet3 at the top of a sort worksheet porcedure?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JohnG

Board Regular
Joined
Feb 18, 2002
Messages
165
Can you be a little more specific about the sheet names.

You could give them names that start with Code (ie Code1, Code2 etc) and have a sort that ignores anything that starts with code.
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
I using a simple procedure to sort the worksheets. But have 3 sheets that use for administrative control over the workbook and I want them to be the first three worksheets on the left when the workbook opens.

Currently they are named CB-List, MenuList, and Intro Sheet, but in the coding I would like to use the sheet code name instead of the sheet name ...

<pre>Sub WS_Sort1()
Dim i As Integer
Dim j As Integer
Dim TotShts As Integer

Application.ScreenUpdating = False
TotShts = Worksheets.Count - 1
If TotShts = 0 Then Exit Sub
For i = 1 To TotShts
For j = i + 1 To TotShts
If Worksheets(j).Name < Worksheets(i).Name Then
Worksheets(j).Move Before:=Worksheets(i)
End If
Next j
Next i
End Sub</pre>
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It would have been quicker if you had posted your code at the outset. Try this:

Code:
Sub WS_Sort2()
    Dim i As Integer
    Dim j As Integer
    Dim TotShts As Integer
    Dim Sh As Worksheet
    Application.ScreenUpdating = False
    For Each Sh In Worksheets
        If Sh.CodeName = "Sheet1" Then
            Sh.Move Before:=Worksheets(2)
        End If
    Next Sh
    For Each Sh In Worksheets
        If Sh.CodeName = "Sheet2" Then
            Sh.Move After:=Worksheets(1)
        End If
    Next Sh
    For Each Sh In Worksheets
        If Sh.CodeName = "Sheet3" Then
            Sh.Move After:=Worksheets(2)
        End If
    Next Sh
    TotShts = Worksheets.Count
    If TotShts <= 4 Then Exit Sub
    For i = 4 To TotShts
        For j = i + 1 To TotShts
            If Worksheets(j).Name < Worksheets(i).Name Then
                Worksheets(j).Move Before:=Worksheets(i)
            End If
        Next j
    Next i
End Sub
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
Andrew - rogggg on code, got in a hurry. Thanks for fix ...
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
It would have been quicker if you had posted your code at the outset. Try this:

Code:
Sub WS_Sort2()
    Dim i As Integer
    Dim j As Integer
    Dim TotShts As Integer
    Dim Sh As Worksheet
    Application.ScreenUpdating = False
    For Each Sh In Worksheets
        If Sh.CodeName = "Sheet1" Then
            Sh.Move Before:=Worksheets(2)
        End If
    Next Sh
    For Each Sh In Worksheets
        If Sh.CodeName = "Sheet2" Then
            Sh.Move After:=Worksheets(1)
        End If
    Next Sh
    For Each Sh In Worksheets
        If Sh.CodeName = "Sheet3" Then
            Sh.Move After:=Worksheets(2)
        End If
    Next Sh
    TotShts = Worksheets.Count
    If TotShts <= 4 Then Exit Sub
    For i = 4 To TotShts
        For j = i + 1 To TotShts
            If Worksheets(j).Name < Worksheets(i).Name Then
                Worksheets(j).Move Before:=Worksheets(i)
            End If
        Next j
    Next i
End Sub

I have some sheets that are prefixed with "-01', "-02", "-03' and need them to be at end of workbook and then sorted according to the 01, 02 and 03 etc...
 

Forum statistics

Threads
1,143,707
Messages
5,720,386
Members
422,282
Latest member
psunith

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