A lot of times, you want to iterate through your worksheets to do something, whether it be exporting information to those worksheets or bringing in information from those worksheets into a central worksheet. In this screencast, I'm just going to show a simple example where we can iterate through our worksheets and do something to that. In particular, I'm just going to put a seven in cell B2 into each of the worksheets. This is the worksheets' starter file that I have on the website. And so, I'm going to go through, I guess in cell, let's do cell B2. So, all of these worksheets, we're going to place the number seven into cell B2. So, I've created a sub here called place seven. I've Dim W as a worksheet just because it's going to be our object in the for each statement that I'm going to use. Then I add this for each. This is similar to a for next loop but if you're doing something to everything in a collection, like worksheets, it's really easy to just use for each. So, for each w, w again was deemed as a worksheet in worksheets, we're going to do something. In particular, we're going to place a seven into range B2 of each of those worksheets. Now, there's a mistake here and I'm going explain what happens when we do this. Remember, when you have multiple worksheets, and you don't have the worksheet in front of the range, then it just acts on the active cell. So, this'll work for the first worksheet, Sheet1, which is the active cell. But then when we go through, it's just replacing B2 of the active worksheet with a seven. So, this is actually not doing what we want it to do. If I open up Sheet2, we don't have the seven, B3, and so on. Instead, what we need to do is we need to put w.range. W then is going to be each of the worksheets here, one at a time. Now, when we run this, I'm just going to press F5 to run it all. Then we can open up all the worksheets and we have a seven in cell B2. We can also do this using a for loop. I've dimmed I as an integer, that's going to be our accounting or iterating index, N as an integer that's going to be the number of worksheets. We can count the number of worksheets using worksheets.count. We enter into a for loop for i equals one to n, and then we put a four, I'm going to use a four instead of a seven, into range B2 of each of our worksheets. And when we run through this, you'll see that, if you go to Sheet1 here, we've replaced that with a four. Sheet 2, and I'm just going to continue. And we then placed a four into each of the cells, B2, of our worksheets. The next thing I'm going to show you how we can change sheets. We can iterate through sheets but only if the name of the sheet meets a particular constraint. So, I'm going to go ahead and name the even sheets here something different. So, I've named the even sheets just Data1, Data2, and Data3. Now, what we want to do is we're going to just replace cell, let's just do B4. We'll put a 10 in there but only for the data sheets. So, what I've done is inside my for loop, I've placed a if then, if left, remember left is a string function, if left of the worksheet.name the first character equals D. That means we're on the Data1, Data2, or Data3 tab. Then, worksheets, that worksheet we're going to place in cell B4, a value of 10. So, we can go here. Let me just go to Sheet 1 and we're going to go through this using F8. So, the first worksheet does not start with a D. The name does not start with a D. The second one it does. So, we can go into Data1 here and we see we've placed a 10 into cell B4 and then we can keep going. So, let's work through a different example. I'm going to go ahead and insert a new workbook. I'm going to insert a new module. And what we're going to do in this example, we're going to ask the user for how many sheets we want to create. For each of those sheets then, it's going to prompt the user to input a name for that sheet. And then we are going to place, we're going to ask the user for their name, and we're going to place the name into cell A1 of each of those sheets. And we're actually going to ask them for their name at the very beginning. So, I've created the sub routine. I'm dimming iteration index i and n, which is going to be the number of sheets that the user provides. I've also dimmed PersonName as a string, that's going to be the name of the person that's going to be placed in cell A1. We, then, obtain the name of the user in an input box. In an input box, we obtain n which is the number of sheets that they want in their workbook. Now, importantly there's already one sheet in the workbook. So, if they entered five, then we're only going to add four more. So, I've added this for loop. We're iterating for i equals one to n minus one. And inside the for loop, we're just going to be adding sheets, n minus one times. We're going to be adding sheets after the active sheet. So, when we run through that, if they input five, then we would add four sheets. Now, we're going to enter into a second for loop, in which we're going to obtain the names of those sheets. And we're going to add the name of that person to cell A1. The first I am going to do inside the for loop is to ask the user for the SheetName. I also need to dim SheetName as a string up here. So, I've dim the SheetName as a string. We obtain the SheetName using an input box. What is the name of sheet and i? So, that will say, "What is the name of Sheet1? What is the name of Sheet2?" And so on. And then we rename that worksheet, worksheet i. The last thing we're going to do inside the for loop, is we're going to, into cell A1, we're going to put the user's name. And we do that using the following line. Worksheets(i).Range ("A1") = PersonName. So, I think we're ready to go. Let's go ahead and step through this, pressing F8. We ask the user for their name. I'll just say, Charlie. Then we get the number of sheets and I'll just do four. And we add. Then, we're going to add our first sheet, call that Sheet2. Then we add a second sheet and a third sheet and we're done. So, we have one through four. Notice we did that for i equals one To n minus one. Now, we iterate through all sheets. We asked them for the name of Sheet1. So, I might just say Data. And then we rename Sheet1, Data, into cell A1. Then of the first worksheet, we put the name and we keep going. The second sheet, we might say basic information, something like that. Then, we rename that and then we keep going. So, by the end of this, I have named my four tabs. And into cell A1 of each of those worksheets, we have put the user name. So, this is how you can work with iterating through worksheets to do different things. Thanks for watching.
