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

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,786
Office Version
  1. 2016
Platform
  1. Windows
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
Andrew - rogggg on code, got in a hurry. Thanks for fix ...
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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