All right, moving along. We are now into the third module of the second part of the course. I'm going to be going over all about worksheets in this screen cast. And then the following screen casts are going to sort of go through examples of how we can iterate through worksheets to do different things. So, let's talk about worksheets. There's a couple ways to look at the data regarding worksheets. Worksheets itself is a collection in VBA which contains all of the worksheets in a workbook. So we can count the worksheets using Worksheets.Count. We can output the name of a worksheet. So that provides the name of the ith worksheet. If we don't know the worksheet name, we can always obtain that worksheet name using ActiveSheet.Name. I've got a starter file on the website called worksheets-STARTER that you can go ahead and download. I've got a couple of different tabs down here, I've got just a main Sheet1, I've got Sheet2, 3, 4, 5, and 6. And we've got different information in those worksheets. In the next several screen casts, I'm going to be using this starter file as we work through examples, but let's just go back to Sheet1. So right now this is the active worksheet, it's sort of like the active cell. So whichever tab is selected is the active worksheet. So if I click on Sheet3, this is the active worksheet. So I'm going to go ahead and insert a module here. I'm just going to call this WorksheetTests (). And the first thing I'm going to do is I'm just going to MsgBox Worksheets.Count and when we run this it just tells us that there are currently six worksheets in our workbook. Now if we wanted to message box the name of the second worksheet. So here we have worksheets and then you can put in parentheses 1 through however many worksheets you have in your workbook. So I can message box that if you want to. So that's named Sheet2. Notice down here, I have Sheet3 as the active worksheet. Another thing I can do is use ActiveSheet.Name. So that then displays the name of the active sheet. For example, if you opened up a workbook and you didn't know the name of the first sheet that popped up. And you wanted to sort of use that in a code, you could write something like sheetname = ActiveSheet.Name, where Dim sheetname As a string. We can also use VBA code to add sheets. You can delete sheets using VBA code. If you want to turn off the alert that pops up, then you can just use Application.DisplayAlerts = False. And then, after that, you would say Sheets, Sheets correspond to the collection of sheets, you can also use worksheets and, Sheet2.Delete. Now, let me just explain real quick the difference between worksheets and sheets. Sheets refer to all the sheets, including charts. So, charts can take up tabs or sheets in your workbook. If you just want to refer to worksheets then you can use worksheets. So, if I want to count only the worksheets without the charts then I'd use Worksheets.Count. If you wanted to count all of the sheets including the sheets that have charts on them, you could use Sheets.Count. We can also rename sheets, we can use sheets("Sheet3"), the current name .Name equals the new name. You could also hide sheets, you can change the visibility by using this statement here. You could also move back and forth between sheets by activating. So you could use Sheets("Sheet2").Activate, that'll activate Sheet2. Or you can refer to it by the number. So Worksheets(2) or you can just do Sheets(2).Activate. And again, the sheets collection includes sheets that have charts on them. So I'm just going to use the Sheets.Add after active sheet command and down here Excel I'm going to just click Sheet6 as our active sheet. And then what I'm going to do is two things, I'm going to rename the active sheet. So once I add a new work sheet that's going to be the active sheet. I'm going to rename that new Sheet Data and I'm also going to name the first worksheet in my workbook Main. So when we do this, as long as Sheet6 is my active sheet, so let's just step through this using F8. So we added a sheet and we see that we've named that Sheet7, it's now the active sheet. We press F8 again and we rename that tab Data. And then we press F8 one more time and the first worksheet, we rename that Main. Now, when you rename worksheets it doesn't change that to the active sheet so still, Data is our active sheet. So in my workbook, on my sheets I have different things that are placed in cell A1. Now, it's really important when you have work books that have multiple sheets it's really important to remove any ambiguity. So let me just show you what I mean here. If I just have a general statement MsgBob Range("A1"), that totally depends upon which work sheet is the active work sheet. So right now Sheet5 is the active worksheet. And if I ran this using F5, it's going to give me is, which is in cell A1 of worksheet 5. When worksheet2 is the active worksheet, then when I press F5 we get Hello, which is in cell A1 of worksheet2, which is the active worksheet. If you specifically want to refer to just one of the worksheets in your workbook, you have to put which worksheet you are referring to in front of range A1. For example, we could do Msgbox sheets those are all the sheets in our workbook. The collection, we're referring to Sheet4.Range ("A1"). So even if sheet2 is my active worksheet when I run this, we've removed the ambiguity and we'd output name which was in cell A1 of sheet4. Just to show you another example of how we can move back and forth between worksheets, we're going to start with sheet4. I'm going to then go over to Sheet6 and copy whatever is in cell C5 and then we're going to move back to Sheet4 and we're going to paste. So I'm going to start on, it doesn't matter what worksheet we start on, we're going to activate Sheet6. We're then going to select Range C5. I'm going to copy this selection and I'm going to activate Sheet4 and finally into range A3 we are going to paste special. So let's go through this again, it doesn't matter what worksheet we start on. I'm going to press F8 one at a time so we are going to activate Sheet6. Notice we bumped into Sheet6 her in Excel and then we going to select range C5, we could also use activate there. Then we're going to copy that, so that 1 in C5 is now on the clip board. We're then going to activate Sheet4 and finally into range A3, we're going to paste special which is the value. So we can place that value into cell A3 and then we end the sub. By the way, a much better way to do this Is this just going to do it direct manipulation here. I have put this all into a single line Sheets("sheet4") .Range("A1") = Sheets("Sheet6") .Range("C5"). This is a much better way to do this and we can just do that in just a single line, oops, that should have been Range("A3"), but you get the idea. Another common thing to do in VBA subroutines related to sheets is to hide the different tabs. Just as an example in normal Excel if you right-click on a tab you can do hide and it's hidden that. To unhide, you can right-click and do Unhide. And then it'll ask you which sheet to unhide. So as an example, you could have a VBA subroutine that has a bunch of sheets hidden. And then depending upon what the user selects, it'll show or unhide different sheets. I can hide Sheet4, and then if you want to make it visible again, you can just change this to true and we can run that and it unhides it. That's it for this screen cast on just basic manipulation of worksheets using VBA.
