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
Posted in Uncategorized

4 thoughts on “Looping Through Sheets

  1. > 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

  2. 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
  3. 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


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

Leave a Reply

Your email address will not be published.