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
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.
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.

Download file

Related Articles:

Delete sheets without confirmation prompts using VBA in Microsoft Excel

Add And Save New Workbook Using VBA In Microsoft Excel 2016

Display A Message On The Excel VBA Status Bar

Turn Off Warning Messages Using VBA In Microsoft Excel 2016

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.