Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
The code that I recorded earlier proved useful. Remember the instruction that was recorded
when I moved a sheet to the first position in the workbook?
Sheets(“Sheet3”).Move Before:=Sheets(1)
After a little thought, I was able to write a For-Next loop that would go through each sheet and
move it to its corresponding sheet location, specified in the SheetNames array:
For i = 1 To SheetCount
Sheets(SheetNames(i)).Move Before:=Sheets(i)
Next i
For example, the first time through the loop, the loop counter i is 1 . The first element in the
SheetNames array is (in this example) Sheet1 . Therefore, the expression for the Move method
within the loop evaluates to
Sheets(“Sheet1”).Move Before:= Sheets(1)
The second time through the loop, the expression evaluates to
Sheets(“Sheet2”).Move Before:= Sheets(2)
I then added the new code to the SortSheets procedure:
Sub SortSheets()
Dim SheetNames() As String
Dim SheetCount as Long
Dim i as Long
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
Call BubbleSort(SheetNames)
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
Before:=ActiveWorkbook.Sheets(i)
Next i
End Sub
I did some testing, and it seemed to work just fine for the Test.xlsx workbook.
 
Search JabSto ::




Custom Search