In this video, we're going to see how we can copy a worksheet using VBA. So to actually copy worksheets in VBA, we need to use the worksheet copy method. And the worksheet copy method has two parameters the before and after parameters, and they work exactly as how they did on the worksheet dot add method. So they actually copy the sheet and put the new copy before or after the worksheet that you would specify on the before or after parameters. Okay, so let's go back to the Visual Basic Editor and see how that works. Okay, so to copy a worksheet, we need to use the worksheet dot copy method.

So we need to actually specify the name of the worksheet that we need to copy. So we're going to write worksheets, sheet one, copy. So that's it. I want to copy sheet one here that I've got. And then when we press face, actually, there's nothing that would make the parameters appear on the screen here. If you open brackets, they won't appear if you press space, they won't appear, so actually just get to know them.

And let's say we're going to insert the new sheet before sheet one. So we're going to write before colon equals and then worksheets sheet one. So what this is going to do is that it's gonna copy sheet one and insert the new copy before sheet one and that is to the left hand side of sheet one. So if I run the code, you can see here that sheet one has been copied. The new name is sheet one, with two between parenthesis and it's been inserted on the left hand side of sheet one. If you use the after parameter, then this is going to insert the new sheet after sheet one which is to the right hand side of sheet one So if you run that, you can see here that the new sheet has been inserted to the right hand side of sheet one.

If you leave the dot copy method without specifying the before after, so if you just delete that, and we run the code, what's going to happen is that sheet one is going to be copied into a new blank workbook. So actually, Excel is going to copy it and put it in a new blank workbook. So if I run the code here, you can see here that a new blank workbook named book one has been created with sheet one copied to it. Okay, and then afterwards, you could save that workbook or do anything to that workbook. And we will learn how to deal with workbooks later on in the course, but just know that if you do not specify the before or after parameters on the dot copy method for the worksheets, you're going to have your worksheets. copied into a new blank workbook.

Okay, so another scenario is that if you want your newly copied sheet to have a different name than sheet one with a number between brackets, for example, so you want to name it my sheet, for example. So how do we do that? Well, I want to show you something here. If you select sheet one and copy it, so I'm going to press Ctrl on my keyboard here, I'm going to copy on the Excel interface, the normal Excel interface. You can see here that the newly created copy cheat here is now the active sheet. So when you copy a sheet, the newly copied sheet is going to be the active sheet the copy is going to be the active sheet.

So we're actually gonna take advantage of that and use an object called the active sheet. So the active sheet is a worksheet object. And it has all the methods and properties of a worksheet or a sheet. So actually, we're going to write active sheet. And then we're going to use the dot name property and change that to whatever name we want. So here, I'm going to change the name of the active sheet, which is going to be the newly compound sheet to my sheet.

So when we run that code, what's going to happen is that sheet one is going to be copied and then it's going to become the active sheet and then we're going to change its name. And actually, if we do if we run the code this way, it's going to be copied to a new workbook. So we would write before for example, calling equals worksheets sheet one, because I don't want a new workbook to be created. I just want it to be copied in this existing workbook. So now in newly copied worksheet is going to be created on the left hand side of sheet one and it's going to be named my sheet. So we will run the code now.

And you can see here that a newly created cheat has been created here and it is on the left hand side of sheet one and it is named my sheet. Okay guys, so that's it for copying worksheets. Thank you very much for watching this video and I'll see you on the next one.