Loop Through All Worksheets & Perform Actions

Home » pub » Tutorials » Loop Through All Worksheets & Perform Actions

Automate repetitive actions across multiple Excel worksheets with this simple VBA tutorial. Learn how to loop through all sheets in a workbook, apply formatting, update values, clear data, protect sheets, or run custom actions on each worksheet. Ideal for Excel users, analysts, finance teams, and professionals who want to save time by automating workbook-wide tasks with VBA.

Iterating through every worksheet in a workbook is one of the most powerful VBA patterns. For Each ws In Worksheets visits every sheet automatically. You can apply formatting, copy data, rename sheets, or protect them — all in a single loop. It works regardless of how many sheets the workbook has.

This guide covers two main loop patterns and six practical examples. These include printing all sheets, clearing data, copying a value to all sheets, renaming from a list, hiding all but one sheet, and collecting data from all sheets into a summary.

Worksheets vs Sheets: Worksheets iterates only worksheet tabs. Sheets iterates all tab types including Chart sheets and Dialog sheets. In most cases, use Worksheets to avoid processing non-data tabs accidentally.

Two Loop Patterns Compared

There are two standard patterns for looping through sheets. For Each is cleaner and does not require an index. For i is useful when you need the sheet number for alignment with a list in another sheet.

Pattern 1: For Each (recommended) Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Debug.Print ws.Name Next ws Pattern 2: For i = 1 To Count (use when index matters) Dim i As Integer For i = 1 To ThisWorkbook.Worksheets.Count Debug.Print ThisWorkbook.Worksheets(i).Name Next i

Examples 1–4: Worksheet Loops in Practice

Print all sheets — skip the Cover tab

This macro loops through every sheet and calls PrintOut on each one. An If statement skips the sheet named "Cover". Similarly, you can skip any sheet by adding its name to the condition.

Sub PrintAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Cover" Then ws.PrintOut Copies:=1 End If Next ws MsgBox "All sheets printed." End Sub

Clear data from all sheets — reset a template workbook

Clearing a specific range on every data sheet is a common template-reset task. ClearContents removes values but leaves cell formatting intact. The loop skips the sheet named "Template" to preserve the master copy.

Sub ClearAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Template" Then ws.Range("B2:D50").ClearContents End If Next ws MsgBox "Data cleared on all data sheets." End Sub

Copy a value to all sheets — synchronise a report date

When a report date in cell A1 must appear on every sheet, a loop is the most reliable approach. The macro reads the value from the Summary sheet and writes it to A1 on every other sheet. This keeps all sheets synchronised without manual copy-paste work.

Sub CopyValueToAllSheets() Dim ws As Worksheet Dim SrcVal As Variant SrcVal = ThisWorkbook.Sheets("Summary").Range("A1").Value For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then ws.Range("A1").Value = SrcVal End If Next ws End Sub

Rename sheets from a list — batch rename via column A

When column A on a "Names" sheet contains the new tab names, this macro renames sheets sequentially using a For i loop. The sheet index aligns with the row number. Sheet names cannot contain characters like / \ * ? : [ ].

Sub RenameSheetsFromList() Dim i As Integer Dim NewName As String Dim NamesWS As Worksheet Set NamesWS = ThisWorkbook.Sheets("Names") For i = 1 To ThisWorkbook.Worksheets.Count NewName = NamesWS.Cells(i, 1).Value If NewName <> "" Then ThisWorkbook.Worksheets(i).Name = NewName End If Next i End Sub

Examples 5–6: Hide Sheets and Collect Summary Data

Hide all sheets except one — presentation mode

Before sharing a file, it is common to hide all sheets except the dashboard. This macro hides every sheet except "Summary" using xlSheetVeryHidden, so users cannot unhide them via the right-click menu. A second macro restores all sheets when needed.

Sub HideAllExceptSummary() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then ws.Visible = xlSheetVeryHidden End If Next ws End Sub Sub ShowAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub

Collect data from all sheets — consolidate to a summary

This macro reads cell B2 from every sheet and writes it to the Summary sheet as a new row. The result is an automatic data consolidation that replaces manual copy-paste from each individual sheet. The Summary range is cleared first to avoid duplicate rows on repeated runs.

Sub CollectDataToSummary() Dim ws As Worksheet Dim SumWS As Worksheet Dim DestRow As Long Set SumWS = ThisWorkbook.Sheets("Summary") SumWS.Range("A2:B100").ClearContents DestRow = 2 For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then SumWS.Cells(DestRow, 1).Value = ws.Name SumWS.Cells(DestRow, 2).Value = ws.Range("B2").Value DestRow = DestRow + 1 End If Next ws MsgBox "Summary updated." End Sub

Common Issues and How to Fix Them

The loop skips a sheet or processes the wrong one

This usually happens when sheets are added or deleted during the loop. For Each is safe because it iterates a snapshot of the collection. However, For i = 1 To Count can skip sheets if you delete a sheet inside the loop, because the Count changes. In that case, loop backward using Step -1 to avoid index-shift errors.

Run-time error 1004 when writing to a protected sheet

The loop is trying to write to a locked cell on a protected sheet. Add an Unprotect call at the start of each iteration and a matching Protect call at the end. Alternatively, protect all sheets using UserInterfaceOnly:=True so VBA can always write without toggling protection inside the loop.

Frequently Asked Questions

How do I loop through all worksheets in VBA?+

Use For Each ws In ThisWorkbook.Worksheets. Declare ws As Worksheet at the top of the Sub. Inside the loop, ws refers to the current sheet on each iteration. To skip specific sheets, add an If ws.Name <> "SheetName" Then condition inside the loop body.
What is the difference between Worksheets and Sheets in VBA?+

Worksheets refers only to standard grid-based worksheet tabs. Sheets refers to all tab types in the workbook, including Chart sheets, Macro sheets, and Dialog sheets. In most looping scenarios, use Worksheets to avoid processing non-data tabs accidentally. Use Sheets only when you specifically need to include Chart sheets or other special tab types.
How do I skip a specific sheet inside the loop?+

Add an If condition inside the loop body. For example: If ws.Name <> "Summary" Then ... End If. To skip multiple sheets, use And: If ws.Name <> "Summary" And ws.Name <> "Cover" Then. This skips both named sheets and processes all others. Alternatively, store excluded sheet names in an array and use a helper function to check membership.
How do I loop through only visible sheets?+

Check the Visible property inside the loop: If ws.Visible = xlSheetVisible Then. This skips hidden sheets (xlSheetHidden) and very hidden sheets (xlSheetVeryHidden). Only visible tabs are processed. This is useful when some sheets are hidden for distribution but should not be modified by bulk macros.