There are a number of ways to loop through the sheets in the workbook. Here are three examples.
In the first example, I create an Object variable and use the For Each construct. As you know, Sheets can be Worksheets, Chart Sheets and Dialog Sheets. They can also be Macro sheets, but those appear to be considered Worksheets in the code. Declaring my variable as Object allows it to become any of those sheet types. Had I declared it as a Worksheet and looped through the Sheets collection, it would complain when it got to a non-Worksheet.
Dim sh As Object
Debug.Print “All Sheets”
Debug.Print “——————————————“
For Each sh In ActiveWorkbook.Sheets
Debug.Print sh.Name
Next sh
Debug.Print
End Sub
In the second example, I declare sh as a Worksheet variable, but I only loop through the Worksheets collection (not the Sheets collection). This is a good way to loop through the worksheets when you want to ignore any sheets that aren’t.
Dim sh As Worksheet
Debug.Print “All Worksheets”
Debug.Print “——————————————“
For Each sh In ActiveWorkbook.Worksheets
Debug.Print sh.Name
Next sh
Debug.Print
End Sub
Recently I was using my Quick Find utility. It looks on every sheet in the workbook starting with the first one. I thought it would be more appropriate to start with the active sheet.
Dim i As Long
Dim sh As Worksheet
Debug.Print “Worksheets Starting with Activesheet”
Debug.Print “——————————————“
For i = ActiveSheet.Index To ActiveWorkbook.Sheets.Count
If TypeName(ActiveWorkbook.Sheets(i)) = “Worksheet” Then
Set sh = ActiveWorkbook.Sheets(i)
Debug.Print sh.Name
End If
Next i
If ActiveSheet.Index > 1 Then
For i = 1 To ActiveSheet.Index – 1
If TypeName(ActiveWorkbook.Sheets(i)) = “Worksheet” Then
Set sh = ActiveWorkbook.Sheets(i)
Debug.Print sh.Name
End If
Next i
End If
End Sub
It consists of two loops. The first loop starts at the active sheet and loops to the end. Then, if the active sheet isn’t the first sheet, the second loop starts at the first sheet and loops through to the sheet just before the active sheet. It seems like there should be a way to do this in one loop, but I couldn’t come up with it. The Index property tells you the position of the sheet relative to the other sheets, starting with 1. As you move sheets around, their Index properties change to reflect where they are. I don’t know of any Index property for just worksheets. That is, the Index reflects the position of the Sheet in the Sheets collection, not the Worksheets collection or ChartSheets collection or any other collection. So I have to test (using Typename) what kind of sheet it is if I care. I didn’t need to care in this example as all of the sheet types have a Name property, but usually I do care.
Here’s the result of running those three procedures:
And now a bonus method (that I personally almost never use). The Sheet object has a Next property. The Next property returns a Sheet object whose Index property is one greater than the calling Object. If there is no Next sheet, it returns Nothing. So you could loop through the sheets like this:
Dim sh As Object
Set sh = ActiveWorkbook.Sheets(1)
Do
Debug.Print sh.Name
Set sh = sh.Next
Loop Until sh Is Nothing
End Sub
> It seems like there should be a way to do this in one loop,
> but I couldn’t come up with it.
One way:
Sub LoopSheets5()
Dim ws As Worksheet
Dim nIndex As Long
Dim nStart As Long
Dim i As Long
nStart = ActiveSheet.Index
With ActiveWorkbook.Sheets
For i = -1 To .Count – 2
nIndex = (nStart + i) Mod .Count + 1
If TypeOf .Item(nIndex) Is Worksheet Then
Set ws = .Item(nIndex)
Debug.Print ws.Name
End If
Next i
End With
End Sub
Oops – forgot the VB tags:
Dim ws As Worksheet
Dim nIndex As Long
Dim nStart As Long
Dim i As Long
nStart = ActiveSheet.Index
With ActiveWorkbook.Sheets
For i = -1 To .Count – 2
nIndex = (nStart + i) Mod .Count + 1
If TypeOf .Item(nIndex) Is Worksheet Then
Set ws = .Item(nIndex)
Debug.Print ws.Name
End If
Next i
End With
End Sub
Hi Dick,
here’s my take at the “one loop” version of LoopSheets3:
Dim sh As Object
Dim idx As Long
Debug.Print “Worksheets Starting with Activesheet”
Debug.Print “——————————————“
idx = ActiveSheet.Index
Set sh = ActiveWorkbook.Sheets(idx)
Do
If TypeOf sh Is Worksheet Then
Debug.Print sh.Name
End If
Set sh = sh.Next
If sh Is Nothing Then
Set sh = ActiveWorkbook.Sheets(1)
End If
Loop Until sh.Index = idx
End Sub
Output:
Worksheets Starting with Activesheet
—————————-
Sheet3
Sheet4
Sheet1
Sheet2
I’ve tested this briefly and it seems to work just fine..
I wouldn’t have made it if it wasn’t for your great tip about the Next property though :-P
best regards
Peder