Create and rename worksheets - Microsoft Excel Tutorial

- [Narrator] One common task that you will perform in Excel VBA is creating new worksheets also because Excel gives very unimaginative names, such as sheet one and sheet two, you can also rename them. I'll show you how to performed both of those tasks in this movie. My sample file is Oh three Oh two, create and rename. That's an Excel macro-enabled workbook that you can find in chapter three folder of the exercise files collection. This workbook has three worksheets, January, February, and March. We won't work with any of the data on the worksheets. So I'll press Alt F11 to move to the visual basic editor. And here you see that I have the outline of a separate team and some code at the bottom that has commented out. We'll work with that code at the end of this movie. For now, I'll just show you how to add a worksheet to a workbook, and the command is very straightforward. We'll use the sheets collection and then a period and the add method. So what this does is adds a new worksheet to the workbook. If I press F5 to run the code, we'll see in the project window, over on the left, that we do have a new worksheet. And if I press Alt F11, we'll see that it was added to the left of the active sheet January. And that is how Sheets dot Add works. If you don't tell it any different, it will add a worksheet to the left of the active sheet. However, you can control where the new worksheet goes. I'll press all Alt F11 to move back to the visual basic editor. And from here, I can set parameters to indicate where I want the new worksheet to appear. So for example, let's say that I want to add a new worksheet to the left of the worksheet named February. To do that I would keep Sheets dot Add. And then I would say before, and then a colon and an equal sign because I'm setting a parameter value. And now I need to identify the worksheet. In this case, I'll identify it by name. And that would be sheets followed by left parenthesis. Then in double-quotes, cause we're typing in a string February followed by double quotes and right parentheses, write down arrow, check my code, everything looks good. So I'll press F5 to run and we have a new worksheet and I'll press Alt F11. And I see here that we have a new worksheet called sheet two, and it is in fact to the left of the worksheet named February. So that's what you can do if where you want to put the worksheet. However, let's say that you want to put a worksheet after the last worksheet on the tab bar, in this case to the right of March. Well, there are a couple of ways you can do that. The first is to use it after, as the keyword and put it after March. However, if you don't know what the last worksheet is named, or if you don't even know how many worksheets there are, then you need to count them. And unfortunately, you can do that from within Excel VBA. So I'll press Alt F11 to move back to the visual basic editor. And we're going to put a new worksheet after the last worksheet in the workbook. So I'm going to change before to after. And then instead of naming the worksheet that we want to put this new worksheet after we're going to count the number of worksheets in the workbook, and that will be sheets dot count. So the count is the property that contains the number of worksheets currently in the workbook. And this will go after that last one. So I'll press F5 to run the code. We've got a new worksheet called sheet three, all press all to F 11, and we can see in fact that it is at the right end of the tab bar. If you want to rename a worksheet, there are a number of ways to do that. I'll press Alt F11 to move back to the visual basic editor. And this is where the code that I have commented out at the bottom comes in handy. So let's say that I want to rename the first sheet in the workbook. That is sheet one. I want you to rename it as April. So I will comment out the after code that I created earlier, and I will remove the single quote. So that sheet one name equal April is now active, press F5 and press Alt F11 to move back to the workbook. And we'll see, in fact, that the first worksheet is named April. We can also refer to the worksheet by its name. So I'll press Alt F11 again. And let's say that I want to rename the worksheet named April to, 2020 April. I already have the code typed in. So I'll just comment out the line we used before and uncomment by deleting the single quote sheets, April dot name equals 2020 April press F5 to run all to F 11 again, and we see that the first worksheet is now named 2020 April. So that's a lot of techniques. However, once you get used to the pattern of using before and after and counting the worksheets in the workbook, it will become very clear.