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.
Click the below link to download the working file:
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.