# 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

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



• This example was developed in Excel 2013 Pro 64 bit.
• Published: 11 August 2015
• Revised: Saturday 23rd of June 2018 - 07:11 PM, [Australian Eastern Standard Time (EST)]