xlf QandA series
How to sort worksheets from range list index
I WANT TO SORT ALL THE WORKBOOK SHEETS. HOW DO I SORT THE WORKSHEETS AND CHARTSHEETS BASED ON AN INDEX IN AN EXCEL RANGE?
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.
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
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)]