How Can I Copy and Rename a Worksheet in the Same Workbook?

ScriptingGuy1

Hey, Scripting Guy! Question

Hey, Scripting Guy! I’d like to copy one of the worksheets in an Excel workbook and then paste that copy into the very same workbook; once it’s pasted it, I’d like to rename it. Can I do that?

— JG

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JG. Of course you can do this; after all, if you can do something in Excel it’s almost 100% guaranteed that you can do it using a script. This is actually pretty easy, with one little catch we’ll talk about right away.

To begin with, it’s very easy to copy a worksheet in Excel; all you have to do is bind to the worksheet and call the Copy method. For example, this script will copy Sheet1 in a workbook named C:\Scripts\Testsheet.xls:

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Testsheet.xls”)
objExcel.Visible = TRUE

Set objWorksheet = objWorkbook.Worksheets(“Sheet1”) objWorksheet.Copy

As you can see, there’s nothing too fancy here. We create an instance of the Excel.Application object and then use the Open method to open the workbook C:\Scripts\Testsheet.xls. We set the Visible property to TRUE (just so we can watch the drama unfold) and create an object reference to Sheet1, the worksheet to be copied:

Set objWorksheet = objWorkbook.Worksheets(“Sheet1”)

We then call the Copy method to create a copy of the worksheet.

This is the tricky part. The preceding script works, but there’s one problem: by default, the Copy method copies the worksheet into a brand-new workbook. We’ll end up with two copies of Sheet1 alright, but one copy will be in Testsheet.xls and the other copy will be in a new workbook. Not what you had in mind. (If you aren’t sure what we’re talking about, give the script a try and you’ll see what we mean.)

So how do we fix this? Well, what have to do is specify an insertion point for the new worksheet. One way to do that is to indicate which sheet the copied worksheet should be inserted after. To do that, we need to create a second object reference. For example, if we want to insert the new worksheet after Sheet3, we need to create an object reference to Sheet3. Thus:

Set objLastSheet = objWorkbook.Worksheets(“Sheet3”)

When we have this second object reference we then pass it as a parameter to the Copy method. This line of code copies Sheet1 and tells Excel to insert the copied worksheet after Sheet3:

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\testsheet.xls”)
objExcel.Visible = TRUE

Set objLastSheet = objWorkbook.Worksheets(“Sheet3”) Set objWorksheet = objWorkbook.Worksheets(“Sheet1”) objWorksheet.Copy, objLastSheet

Note what happens when we do this; we get a copy of Sheet1 — named Sheet1 (2) — inserted immediately after Sheet3, just the way we wanted:

Microsoft Excel


Having copied and inserted the new worksheet all that’s left is to rename Sheet1 (2). That takes two lines of code: we create an object reference to the new worksheet and then assign it a new name. Or, to put it programmatically:

Set objWorksheet = objWorkbook.Worksheets(“Sheet1 (2)”)
objWorksheet.Name = “My New Worksheet”

Like we said, once we got past the tricky part, this was almost too easy. Here’s a completed script that carries out both tasks for you: it copies Sheet1, and then renames it to My New Worksheet:

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\testsheet.xls”)
objExcel.Visible = TRUE

Set objLastSheet = objWorkbook.Worksheets(“Sheet3”) Set objWorksheet = objWorkbook.Worksheets(“Sheet1”) objWorksheet.Copy, objLastSheet

Set objWorksheet = objWorkbook.Worksheets(“Sheet1 (2)”) objWorksheet.Name = “My New Worksheet”

If you’re looking for more tips and tricks for scripting Microsoft Office check out Office Space, published every Tuesday and Thursday in the Script Center.

0 comments

Discussion is closed.

Feedback usabilla icon