How To Loop Through Sheets In Excel Using VBA

While automating the usual boring tasks of excel in VBA, you’ll get the need to loop through each sheets. And that is why you are here, of course.

Let’s see the code to loop through sheets in a workbook.

VBA Code To Loop in all sheets in Activeworkbook and print sheets Name

Sub loopSheets()
    For Each ws In ActiveWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

The above vba code, loops through each sheet in active workbook and prints the sheets name
0088
How it works?

It is quite simple. We tell VBA to store each worksheet in active workbook in ws and then print its name using name method.

Loop Through All Sheets Except One

So, if you are trying to merge multiple sheets in one master sheet, you will need to loop through each sheet. copy each sheet’s data in master sheet. But you would want to except the master sheet from looping. Let’s say you named master sheet as “Master”. In that case use this method:

Sub loopSheets()
   
    For Each ws In ActiveWorkbook.Worksheets
      If ws.Name <> "Master" Then
         Debug.Print ws.Name & " Copied"
      End If
    Next ws

End Sub

It is almost same as the above code, with only addition of If statement.

If ws.Name <> "Master" Then

This line checks if the current worksheet’s name is “Master” . if it’s not, the code between if block runs. Else the code within if the block is skipped.
0089
So yeah guys, this how you can loop through worksheets in excel. Next we will learn how to consolidate multiple sheets into one using vba.