xlf QandA series

How to sort worksheets from range list index

QUESTION

I WANT TO SORT ALL THE WORKBOOK SHEETS. HOW DO I SORT THE WORKSHEETS AND CHARTSHEETS BASED ON AN INDEX IN AN EXCEL RANGE?


Answer icon Answer

To answer this question we will assume that the sheet names are stored in an Excel range name SheetList with workbook scope. Two techniques for reading the list and performing the sort are demonstrated: a For Each loop construct, and a For Next loop construct.


Using a For Each loop

The For ... Each construct runs a set of statements once for each element in a collection. In the code 1 procedure, the OrderSheets macro uses the SheetList range as the control structure. The loop runs code statements for each element (cell) in the range (line 5) and executes the Move method (line 7). The Move method included in the code statement will perform the actual sort. The loop control variable, \(i\), tracks the number of repetitions and acts as the index to the sheets collection.

If the SheetList range is multidimensional, then the cells in the range are processed in row order.

Notice that the Cell variable is defined as a Range object type.



Code 1: A macro using a For ... Each loop 
'' A multicolumn SheetList is read row by row
Sub OrderSheets()
Dim Cell As Range
Dim i As Integer    ' Initial value, i = 0
    For Each Cell In Range("SheetList")
        i = i + 1
        Sheets(Cell.Value).Move Before:=Sheets(i)
    Next Cell
End Sub

Using a For loop

The For ... Next construct performs the loop a set number of times. The number of repetitions is based on the number of elements in the control structure. This requires some code to count the number of rows in the SheetList range. In this part of the answer, we assume that the SheetList range is a column vector.

In the code 2 macro OrderSheets2, the number of rows in the source is returned by the Count statement in line 7. The For ... Next loop repeats the Move statement (line 10) for each row in the SheetList range.



Code 2: A macro using a For ... Next loop 
'' Only row numbers are counted
'' Therefore, only the first column of the SheetList will be read
''
Sub OrderSheets2()
Dim NoRows As Long, i As Integer
Dim SheetName As String
    NoRows = Range("SheetList").Rows.Count
    For i = 1 To NoRows
        SheetName = Range("SheetList").Cells(i, 1).Value
        Sheets(SheetName).Move Before:=Sheets(i)
    Next i
End Sub