Sorting select sheets between specific sheets

Question

I have a workbook that has four 'system' sheets and each system sheet has a dynamic number or subsequent tabs used for various data collection. What I need is a way to sort the systems and their children sheets in alphabetical order but existing between two specific sheets. Example:

[Intro/Reference Sheet] [Customer info Sheet] [System 1] [Sys1 Child 1-12] [System 2] [Sys2 Child 1-12] [System 3] [Sys3 Child 1-12] [System 4] [Sys4 1-12] [Other sheets]

I've tried sorting subs such as

For Each w1 In Sheets
 For Each w2 In Sheets
  If w1.Range("Z1") <> "" And w2.Range("Z1") <> "" Then
   If w2.Range("Z1").Value < w1.Range("Z1").Value Then
    If w2.Range("Z1").Value = "S1L0" Then
     w2.Move after:=Cover
    Else
     w2.Move before:=w1
    End If
   End If
  End If
 Next w2
Next w1

It starts to work great but then the loop gets out of hand and I have the one sheet with "S1L0" in the target range where it should be and everything else is at the end of the list. Any ideas on how I can get this to work?

EDIT: The system sheets all begin with "S1" to "S4" but they get renamed to "S1 - %string%" to "S4 - %string%", the children sheet are copies of a hidden sheet that are named initially "S#L#" where the # is the system and the child number respectively. the children sheets can also be renamed but it will follow the pattern "S#L# - %string%"

Without your relevant data sample it's quite difficult to realize what's wrong. — Peter L.
Can you give more details about how you go about deciding which sheet is which - it seems you look at range "Z1", but you don't give the specific rules you've implemented to know which is a system / child sheet... — John Bustos
I apologize, the system sheets all begin with "S1" to "S4" but they get renamed to "S1 - %string%" to "S4 - %string%", the children sheet are copies of a hidden sheet that are named initially "S#L#" where the # is the system and the child number respectively. the children sheets can also be renamed but it will follow the pattern "S#L# - %string%" — Noah Rainey

Peter Albert Peter Albert · Accepted Answer · 2013-02-26T19:36:18

This should do the job:

Sub SortSheets()
    Dim lngParentID As Long, lngChildID As Long
    Dim wsParent As Worksheet, wsChild As Worksheet, wsLast As Worksheet

    Set wsLast = Sheets("Customer Info Sheet")
    For lngParentID = 1 To 4
        Set wsParent = GetSheet(lngParentID)
        If Not wsParent Is Nothing Then
            wsParent.Move After:=wsLast
            Set wsLast = wsParent

            For lngChildID = 1 To 12
                Set wsChild = GetSheet(lngParentID, lngChildID)
                If Not wsChild Is Nothing Then
                    wsChild.Move After:=wsLast
                    Set wsLast = wsChild
                End If
            Next lngChildID
        End If
    Next lngParentID
End Sub

Private Function GetSheet(lngParentID As Long, Optional lngChildID As Long = 0) As Worksheet
    Dim ws As Worksheet

    For Each ws In Sheets
        If (lngChildID = 0 And (ws.Name = "S" & lngParentID Or Left(ws.Name, 4) = "S" & lngParentID & " -")) Or _
            Left(ws.Name, 5) = "S" & lngParentID & "L" & Format(lngChildID, "00") Then
            Set GetSheet = ws
            Exit Function
        End If
    Next
End Function

Sorting select sheets between specific sheets

1 Answers