Looping Through Sheets

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.

Sub LoopSheets1()

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.

Sub LoopSheets2()

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.

Sub LoopSheets3()

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:

Sub LoopSheets4()

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

4 thoughts on “Looping Through Sheets”

> 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:

Public Sub LoopSheets()
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:

Sub LoopSheets3_oneLoop()

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

Pingback: VBA - Is this SheetX a worksheet or chartsheet

Posting code? Use <pre> tags for VBA and <code> tags for inline.