{"id":70133,"date":"2005-03-30T17:01:00","date_gmt":"2005-03-30T17:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/03\/30\/how-can-i-copy-and-rename-a-worksheet-in-the-same-workbook\/"},"modified":"2005-03-30T17:01:00","modified_gmt":"2005-03-30T17:01:00","slug":"how-can-i-copy-and-rename-a-worksheet-in-the-same-workbook","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-copy-and-rename-a-worksheet-in-the-same-workbook\/","title":{"rendered":"How Can I Copy and Rename a Worksheet in the Same Workbook?"},"content":{"rendered":"

\"Hey, \n

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

— JG<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, JG. Of course you can do this; after all, if you can do something in Excel it\u2019s almost 100% guaranteed that you can do it using a script. This is actually pretty easy, with one little catch we\u2019ll talk about right away.<\/P>\n

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

Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Testsheet.xls”)\nobjExcel.Visible = TRUE<\/p>\n
Set objWorksheet = objWorkbook.Worksheets(“Sheet1”)\nobjWorksheet.Copy\n<\/PRE>\n
As you can see, there\u2019s nothing too fancy here. We create an instance of the Excel.Application<\/B> object and then use the Open<\/B> method to open the workbook C:\\Scripts\\Testsheet.xls. We set the Visible<\/B> property to TRUE (just so we can watch the drama unfold) and create an object reference to Sheet1, the worksheet to be copied:<\/P>
Set objWorksheet = objWorkbook.Worksheets(“Sheet1”)\n<\/PRE>\n
We then call the Copy<\/B> method to create a copy of the worksheet.<\/P>\n
This is the tricky part. The preceding script works, but there\u2019s one problem: by default, the Copy method copies the worksheet into a brand-new workbook. We\u2019ll 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\u2019t sure what we\u2019re talking about, give the script a try and you\u2019ll see what we mean.)<\/P>\n
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:<\/P>
Set objLastSheet = objWorkbook.Worksheets(“Sheet3”)\n<\/PRE>\n
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:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\testsheet.xls”)\nobjExcel.Visible = TRUE<\/p>\n
Set objLastSheet = objWorkbook.Worksheets(“Sheet3”)\nSet objWorksheet = objWorkbook.Worksheets(“Sheet1”)\nobjWorksheet.Copy, objLastSheet\n<\/PRE>\n
Note what happens when we do this; we get a copy of Sheet1 — named Sheet1 (2)<\/B> — inserted immediately after Sheet3, just the way we wanted:<\/P>\"Microsoft \n

Having copied and inserted the new worksheet all that\u2019s 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:<\/P>
Set objWorksheet = objWorkbook.Worksheets(“Sheet1 (2)”)\nobjWorksheet.Name = “My New Worksheet”\n<\/PRE>\n
Like we said, once we got past the tricky part, this was almost too<\/I> easy. Here\u2019s a completed script that carries out both tasks for you: it copies Sheet1, and then renames it to My New Worksheet<\/B>:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\testsheet.xls”)\nobjExcel.Visible = TRUE<\/p>\n
Set objLastSheet = objWorkbook.Worksheets(“Sheet3”)\nSet objWorksheet = objWorkbook.Worksheets(“Sheet1”)\nobjWorksheet.Copy, objLastSheet<\/p>\n
Set objWorksheet = objWorkbook.Worksheets(“Sheet1 (2)”)\nobjWorksheet.Name = “My New Worksheet”\n<\/PRE>\n
If you\u2019re looking for more tips and tricks for scripting Microsoft Office check out Office Space<\/B><\/A>, published every Tuesday and Thursday in the Script Center.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I\u2019d like to copy one of the worksheets in an Excel workbook and then paste that copy into the very same workbook; once it\u2019s pasted it, I\u2019d like to rename it. Can I do that?— JG Hey, JG. Of course you can do this; after all, if you can do something in […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5],"class_list":["post-70133","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I\u2019d like to copy one of the worksheets in an Excel workbook and then paste that copy into the very same workbook; once it\u2019s pasted it, I\u2019d like to rename it. Can I do that?— JG Hey, JG. Of course you can do this; after all, if you can do something in […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=70133"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70133\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=70133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=70133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=70133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}