Hey guys, welcome back. So in this video we're going to speak about the for each loop. So the for each loop enables us to loop through objects in a collection of objects. So for example, we can loop through all the worksheets. In a collection of worksheets or the worksheets collection, we can loop through all the cells in a range. Because a range is essentially a collection of cells, we can loop through all the workbooks in the workbooks collection as well.

We can loop through all the charts in the charts collection. So it enables us to loop through each object in a collection of objects. And usually you will have the relationship between the object and the collection as being just the singular and the plural. So for example, worksheet and work sheets worksheet is the object and the collection of objects is the work sheets collection. Okay, so it's gonna be pretty easy. I'm pretty consistent in most of the cases.

So the structure of the for each loop is basically for each object in collection, and then you would have some code to perform. And then next object, and here, the object off to next is optional, you don't have to write the object. So this is a structure for each object in collection. So for each worksheet in worksheets collection, for example, some code that you're gonna apply on it, and usually you would have the object itself referenced in that code. So you're going to, for example, display the name for that particular object. So for each worksheet, just worksheet name, we put it inside a message box or debug print for that name or so.

And then next object so this is the structure of the for each loop. So let's see an example of the for each loop. Okay guys. So now we're going to have an example for the for each loop and this example what it does is that it loops through all the worksheets in the active workbook. So here first of all, we've created an object variable that is called Ws and its type is going to be a worksheet. And then here we're saying for each Ws, which is basically a worksheet in worksheets and this worksheets would refer to the worksheets collection in the current active workbook.

Okay, so this will work on the active workbook not essentially, on the workbook that the macros is written in. So you need to make sure that the workbook that you need to loop through its worksheets is the one that is active. If you want to make sure That you would loop through the worksheets. In the workbook that the macros written, you would write this workbook dot worksheets, or whatever workbook dot worksheets. Okay, and we've learned before how to refer to a workbook on previous videos, so this will make sure that you would loop through the worksheets in the workbook that the macro is written in. So what's gonna happen here, when we loop through the worksheets, we're gonna actually not select each worksheets because you're gonna see here when we loop through, if you look on the bottom left corner here, you're not gonna see that the worksheets are being selected.

So looping through the worksheets does not essentially mean selecting, okay, so we will not select them. We can do stuff to the worksheets that are allowed without selecting the worksheets, but we do not have to essentially select them and then we have the next Ws statement here. That basically enabled us to go to the next worksheet. And if all of the worksheets are finished, then we will basically exit the loop. And please note as well that the Ws part here is optional, you don't have to write next Ws you could write next, but being explicit is a good practice. Okay, now let's run through the loop step by step here.

So I'm going to press f8 here for each worksheet in this workbook worksheets, and then debug dot print Ws dot name. What this is going to do is that it's going to print the name of the worksheet in the immediate window here below. So we're going to execute this step, we've started looping and we first of all loop through sheet one as the first sheet to loop through and we type this name here in the immediate window, and then we're going to loop through the next seat and we typed the name of sheet two and notice here that she has not been selected. Okay? If you need to select the sheet, then you need to type a statement that says Ws dot Activate to activate the worksheet. And then if we continue looping, we would look through the third sheet and this is the last sheet so we're not gonna loop anymore, we're just gonna exit the for each loop and and sub.

So this is how you can loop through worksheets in a collection of worksheets, okay, or basically in a workbook. Now, I want to show you something else as well, which is what happens if you leave it like that as for each Ws in worksheets instead of this workbook dot worksheets instead of being specific. What's going to happen here is that you're going to loop through the worksheets in the current active workbook. So currently, we've got the workbook called for each loop that has sheets one, two, and three as the active workbook so we will actually have the names of these sheets typed here in the immediate window. I've got another workbook that is called book one. You Has sheets called one x two X and three x.

Now if we go through the macro this way, we will actually type the names of sheets, one x and two X and three x because Book One is the active workbook. So here if you leave it like just worksheets and you don't specify in which workbook is just gonna loop through the worksheets in the active workbook, okay, so basically it's a better practice to specify which workbook contains the worksheets that you need to loop through. Okay, this is a better practice and is going to make you avoid problems. On the next video, we're going to see how to loop through more objects.