Looping Through Worksheets in VBA

The Power of Looping Through Worksheets

In accounting, workbooks rarely contain just one sheet. A typical financial model or reporting pack might include:

• A sheet for raw data (e.g., a trial balance export).
• Separate sheets for each department's budget.
• Twelve individual sheets for monthly P&L statements.
• A summary sheet with dashboards and charts.

Being able to programmatically visit each of these sheets to perform an action is essential. You could use a loop to apply consistent formatting, protect completed reports, or gather data for a consolidated summary—all common tasks in a finance role.

The primary method we'll use is the For Each...Next loop, which is designed to work with collections of objects, such as all the worksheets in a workbook.

Your First Worksheet Loop: A Visual Guide

Let's start with a video that clearly demonstrates the basic syntax and shows the loop in action. This example will print the name of each worksheet to the "Immediate Window" in the VBA editor, which is a great way to confirm your loop is working as expected.

Understanding the For Each...Next Loop Structure

The video gave you a practical look at the code. Now, let's take a moment to understand the components of this loop more formally. A For Each loop has a very readable, logical structure.

After this reading, you should be comfortable with the syntax and the role of each line in the loop. The key is that the ws variable becomes a temporary alias for each worksheet, one by one, allowing you to apply methods and properties to it (like ws.Name or ws.Protect).

Adding Intelligence: Conditional Logic in Your Loop

Looping through every sheet is useful, but often you'll want to exclude certain sheets. For instance, you might want to format all your monthly data sheets but ignore a "Summary" or "Instructions" sheet. This is where combining your loop with an If...Then statement becomes critical.

You can check a property of the worksheet, such as its name, before deciding whether to run your code.

' An example of conditional logic inside a loop
For Each ws In ThisWorkbook.Worksheets
    ' Check if the worksheet name is NOT "Summary"
    If ws.Name <> "Summary" Then
        ' If it's not the summary sheet, perform an action.
        ' For example, protect the worksheet:
        ws.Protect
    End If
Next ws

The image below shows exactly this kind of logic within the Visual Basic Editor. The code loops through worksheets but includes an If statement to explicitly skip a sheet named "Master".

A Practical Accounting Macro: Securing Monthly Reports

Let's write a complete macro for a common accounting task. Imagine you have a workbook containing monthly transaction sheets (e.g., "Jan-Data", "Feb-Data") and a master "Chart of Accounts" sheet. Before sending it for review, you want to ensure all the data sheets are visible and protected to prevent accidental changes, while leaving the "Chart of Accounts" sheet unprotected for reference.

Here is the code to accomplish this:

Sub SecureMonthlyDataSheets()
    ' Purpose: Loops through all worksheets. Makes data sheets visible
    '          and protects them, while ignoring the master accounts sheet.

    ' 1. DECLARE THE WORKSHEET VARIABLE
    Dim ws As Worksheet

    ' 2. LOOP THROUGH THE WORKSHEETS COLLECTION
    For Each ws In ThisWorkbook.Worksheets
        
        ' 3. APPLY CONDITIONAL LOGIC
        ' Check if the current sheet is the "Chart of Accounts"
        If ws.Name <> "Chart of Accounts" Then
            ' If it's any other sheet, perform these actions:
            
            ' a) Ensure the worksheet is visible
            ws.Visible = xlSheetVisible
            
            ' b) Protect the worksheet. The empty password means it's
            '    protected from accidental edits but easily unprotected.
            ws.Protect Password:=""
            
        End If
        
    ' The 'Next' statement tells VBA to move to the next sheet in the collection
    Next ws

    ' 4. CONFIRMATION MESSAGE
    MsgBox "All data sheets have been made visible and are now protected."

End Sub

This macro is a perfect example of automation saving you from a tedious, manual task. You could run this on a workbook with 3, 12, or 50 data sheets, and it would work just the same.

An Alternative Method: The For i = 1 To ... Loop

While For Each is often the most readable way to loop through objects, there is another method that uses a counter. This is the For...Next loop you've seen before, but applied to the Worksheets collection.

The structure looks like this:

Dim i As Long
For i = 1 To ThisWorkbook.Worksheets.Count
    ' Action is performed on Worksheets(i)
    Debug.Print ThisWorkbook.Worksheets(i).Name
Next i

Here's how it works:

• Worksheets.Count gives the total number of worksheets in the workbook.
• The loop iterates from 1 to that total count.
• Inside the loop, you refer to the worksheet by its index number: Worksheets(1), Worksheets(2), and so on.

Both For Each and For i = 1 To Count achieve the same result of iterating through all sheets. The For Each syntax is generally preferred for its clarity when you don't need the index number.

Conclusion

You have now added a critical tool to your VBA automation arsenal. The ability to loop through worksheets allows you to manage and process entire workbooks, moving you beyond single-sheet macros.

Key Takeaways:

• The For Each ws In ThisWorkbook.Worksheets ... Next ws loop is the standard, readable method for iterating through all sheets in the workbook containing your code.
• Combining this loop with If...Then statements allows you to selectively apply actions, such as formatting or protecting, only to the sheets that meet your criteria.
• Properties like ws.Name, ws.Visible, and methods like ws.Protect can be used inside the loop to manipulate each worksheet.
• An alternative loop structure, For i = 1 To Worksheets.Count, allows you to iterate using an index number, though For Each is often more intuitive.

In our next lesson, we will address another fundamental challenge in data processing: how to write VBA code to dynamically find the last-used row in a column. This is essential for macros that need to append new data to a list without overwriting existing entries or leaving gaps.