Vba Loop Through Each Sheet

Looping through each sheet in a workbook is a common task in Excel VBA, allowing you to perform actions on every worksheet in your workbook. This can be particularly useful for tasks such as formatting, data manipulation, or even more complex operations like data consolidation. In this article, we'll explore how to loop through each sheet in a workbook, including examples and explanations to help you understand and apply this technique effectively.

To start working with VBA, you need to open the Visual Basic for Applications editor. You can do this by pressing Alt + F11 in Excel, or by navigating to the Developer tab and clicking on Visual Basic. If the Developer tab is not visible, you can add it by going to File > Options > Customize Ribbon and checking the Developer checkbox.

Basic Loop Through Each Sheet

The most straightforward way to loop through each sheet in a workbook is by using a For Each loop. Here's a basic example that will loop through each sheet and display its name in a message box:

Sub LoopThroughSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        MsgBox ws.Name
    Next ws
End Sub

In this example, ThisWorkbook refers to the workbook that the code is running in. The For Each loop iterates over the Worksheets collection of ThisWorkbook, with ws representing each worksheet in turn.

Performing Actions on Each Sheet

Looping through sheets becomes more useful when you perform specific actions on each one. For instance, you might want to change the font of all cells in every sheet to a specific type and size. Here's how you could do that:

Sub ChangeFontOnAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Font.Name = "Calibri"
        ws.Cells.Font.Size = 11
    Next ws
End Sub

This code changes the font of all cells on each sheet to Calibri, size 11.

Skipping Specific Sheets

Sometimes, you might want to skip certain sheets during your loop. You can do this by using an If statement to check the name of the current sheet and decide whether to skip it. Here's an example that skips the sheet named "Summary":

Sub LoopSkippingSummarySheet()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            ' Perform actions here, e.g., changing font
            ws.Cells.Font.Name = "Calibri"
            ws.Cells.Font.Size = 11
        End If
    Next ws
End Sub

Looping Through All Sheets Including Non-Worksheet Sheets

The Worksheets collection only includes worksheet objects. If you want to loop through all types of sheets, including chart sheets, you can use the Sheets collection instead:

Sub LoopThroughAllSheets()
    Dim sh As Object
    For Each sh In ThisWorkbook.Sheets
        MsgBox sh.Name
    Next sh
End Sub

Note that sh is declared as an Object because the Sheets collection can contain both Worksheet and Chart objects.

Practical Example: Data Consolidation

A more practical example of looping through sheets might involve consolidating data from multiple sheets into one. Let's say you have several sheets, each with a table in the range A1:B10, and you want to copy all this data into a new sheet named "Consolidated".

Sub ConsolidateData()
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim sourceRange As Range
    
    ' Create or set the target sheet
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Worksheets("Consolidated")
    If targetSheet Is Nothing Then
        Set targetSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        targetSheet.Name = "Consolidated"
    End If
    On Error GoTo 0
    
    lastRow = 1
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Consolidated" Then
            Set sourceRange = ws.Range("A1:B10")
            sourceRange.Copy Destination:=targetSheet.Range("A" & lastRow)
            lastRow = lastRow + sourceRange.Rows.Count
        End If
    Next ws
End Sub

This code creates a new sheet named "Consolidated" if it doesn't exist and then copies the data from the range A1:B10 of every other sheet into this new sheet, stacking the data vertically.

Gallery of VBA Loop Examples

Gallery of Excel VBA

VBA Gallery

FAQs

What is VBA in Excel?

+

VBA stands for Visual Basic for Applications. It's a programming language built into Excel that allows users to create and automate tasks, from simple macros to complex applications.

How do I open the VBA editor in Excel?

+

You can open the VBA editor by pressing Alt + F11, or by navigating to the Developer tab and clicking on Visual Basic.

What is the difference between Worksheets and Sheets in VBA?

+

The Worksheets collection only includes worksheet objects, while the Sheets collection includes all types of sheets, such as worksheets and chart sheets.

Whether you're automating routine tasks, consolidating data, or creating complex applications, looping through sheets is a fundamental skill in Excel VBA. By mastering this technique, you can significantly enhance your productivity and the efficiency of your workflows in Excel. We invite you to explore more examples, practice with different scenarios, and share your experiences or questions in the comments below. Your feedback and engagement are invaluable in creating a supportive community for learning and growth.