Loop Through All Sheets in Workbook in Excel VBA

In this post, you’ll be learning about loops in Worksheet and a quick tip showing how you can loop through all sheets in workbook in Excel VBA.

Loop through all sheets in Workbook in Excel VBA

Looping through all sheets in a Workbook in Excel VBA can be done using For each loop.

Syntax for ‘for each’ in Loop through Every Sheet in Workbook

Here is the Syntax for ‘for each’ in Loop Through Every Sheet in Workbook

Syntax

For Each ws In Worksheets
'statements
Next

Now let us see this with an example

Sample program using ‘for each’ for updating every worksheet in workbook using VBA.

Code:

Sub Loop_Sheets_Except_One()
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Master" Then
MsgBox ("Sheet looped to master sheet")
End If
Next ws
End Sub

How to Loop Through All Sheets Except One?

When you try to merge multiple sheets in one sheet which is a master sheet, you need to loop through each sheet. Copy each sheet’s data in the master sheet. But you want to avoid the Master sheet in the loop.

So to do this. Let’s Name the master sheet as “Master”.

Code:

Sub Loop_Sheets_Except_One()

       For Each ws In ActiveWorkbook.Worksheets

      If ws.Name <> "Master" Then

         Debug.Print ws.Name & " Copied"

      End If

    Next ws

End Sub