asked on

Sheet tabs in the right order?

The following is a recorded procedure that put my sheet tabs in the right order. However when I insert sheet tabs it messes things up. Is there a way to put sheet tabs in a specific order based on a sheet tab name?

Sheets("Past Due").Select
    Sheets("Past Due").Move Before:=Sheets(3)
    Sheets("Past Due Bruce").Select
    Sheets("Past Due Bruce").Move Before:=Sheets(4)
    Sheets("Non Past Due").Select
    Sheets("Non Past Due").Move Before:=Sheets(4)
    Sheets("Non Past Due").Select
    Sheets("Non Past Due").Move Before:=Sheets(6)
    Sheets("Recovery Date").Select
    Sheets("Recovery Date").Move Before:=Sheets(6)
    Sheets("Original").Select
    Sheets("Original").Move Before:=Sheets(7)
    Sheets("Orders").Select
    Sheets("Orders").Move Before:=Sheets(8)
    Sheets("Recovery Date").Select

Open in new window

John

When I insert a sheet, I use the Insert Before, End, etc. and the sheet goes where I want. In Excel itself, it does not sort sheet tabs by name. There may be a macro to do it but Excel will not.

Flyster

What is the right order? If you want ascending order you can use this macro:

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

Open in new window

Flyster

RWayneH

ASKER

I have a bunch of sheet moving around, added and what not. I thought the instead of going through all the code and checking the place before... That I could do a global sub at the end that said, Put DSC sheet tab first, put Original sheet tab second... etc. I delete and insert so many times that placing them by sheet name many be easier.

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

Thanks