Sheet tabs in the right order?

Posted on 2014-08-29
Last Modified: 2014-08-29
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").Move Before:=Sheets(7)
    Sheets("Orders").Move Before:=Sheets(8)
    Sheets("Recovery Date").Select

Open in new window

Question by:RWayneH
    LVL 89

    Expert Comment

    by:John Hurst
    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.
    LVL 22

    Expert Comment

    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


    Author Comment

    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.
    LVL 27

    Accepted Solution

    You can actually reference the sheet name in the Move method.  But you don't need to do that here.

    If you want to order the sheets at the end of your processing, this code will sort them.
    Sub Order_Sheets()
        Dim arrSheets() As String
        Dim strSheets As String
        Dim x As Integer
        'List all sheets in order separated by commas (no space after comma)
        strSheets = "Past Due,Past Due Bruce,Non Past Due,Recovery Date,Original,Orders,Misc"
        arrSheets = Split(strSheets, ",")
        For x = 1 To UBound(arrSheets)
            Sheets(arrSheets(x)).Move after:=Sheets(ActiveWorkbook.Sheets.Count)
        Next x
    End Sub

    Open in new window

    You just need to create a list of all the worksheets in the order needed (line 7 of the code snippet). You don't have to list them all, but if you don't, the unreferenced sheets will appear before the ordered ones do.

    Example file - with unordered sheets - attached.

    Author Closing Comment


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now