{"id":66453,"date":"2006-09-18T17:19:00","date_gmt":"2006-09-18T17:19:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/09\/18\/how-can-i-copy-a-worksheet-to-a-new-spreadsheet\/"},"modified":"2006-09-18T17:19:00","modified_gmt":"2006-09-18T17:19:00","slug":"how-can-i-copy-a-worksheet-to-a-new-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-copy-a-worksheet-to-a-new-spreadsheet\/","title":{"rendered":"How Can I Copy a Worksheet to a New Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I copy a worksheet in an existing Excel spreadsheet to a new spreadsheet?

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

Hey, TY. Before we tackle your question we wanted to note that, as we write this, the University of Washington football team has a winning record. Sure, we\u2019re only three weeks into the college football season, and the UW\u2019s record is only 2-1, but the way the last two years have gone (3 wins and \u2013 it hurts to type this \u2013 19 losses) a 2-1 record and a 21-20 victory over Fresno State is definitely something worth mentioning. So we\u2019re mentioning it.<\/P>\n

And, let\u2019s face it: if we\u2019re going to talk about having a winning record we need to do it now, without waiting. Not that we lack faith in the Huskies or anything, but, still ….<\/P>\n

By the way, have we mentioned that basketball season is just two months away, and that the UW has been to the men’s Sweet 16 each of the past two seasons? That’s good; after all, mentioning something like that would sound like bragging, and the Scripting Guys never brag.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. OK, granted, that\u2019s because the Scripting Guys never actually have anything to brag about<\/I>. But no one has to know that.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

If you\u2019re a college football fan (and you should be), then you know that winning football games is tough; the Huskies, for example, had to block a extra-point try in the closing minutes in order to beat Fresno State. Fortunately, copying a worksheet from an existing Excel spreadsheet to a brand-new spreadsheet is far easier.<\/P>\n

Anyway, the important thing is that the Huskies finally started to \u2013 what\u2019s that? Show you how to copy a worksheet from an existing Excel spreadsheet to a brand-new spreadsheet? Well, we\u2019re not sure what that has to do with college football, but, hey, why not:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(“Sheet1”)\nobjWorksheet.Activate<\/p>\n
objWorksheet.Copy()\n<\/PRE>\n
See, we told you it was easy. The script starts out by creating an instance of the Excel.Application<\/B> object, and then sets the Visible<\/B> property to True; that gives us a running instance of Excel that we can see onscreen. With Excel up and running we then use the Workbooks.Open<\/B> method to open the file C:\\Scripts\\Test.xls. With the spreadsheet open we use these two lines of code to create an object reference to Sheet1 (the worksheet we want to copy), then use the Activate<\/B> method to make Sheet1 the active worksheet:<\/P>
Set objWorksheet = objWorkbook.Worksheets(“Sheet1”)\nobjWorksheet.Activate\n<\/PRE>\n
Believe it or not, all we need to do now is add one more line of code and we’re done. (Hey, it’s Monday; surely you weren’t expecting us to write a long and complicated script on a Monday, were you?) After we\u2019ve connected to Sheet1 and made this the active worksheet all we have to do is call the Copy<\/B> method:<\/P>
objWorksheet.Copy()\n<\/PRE>\n
That\u2019s it: any time you call the Copy method without any parameters the worksheet will be copied and pasted into a brand-new spreadsheet. You’ll now have two spreadsheets up and running: Test.xls, and a new, untitled spreadsheet that includes an exact copy of Sheet1, including formatting, formulas, and anything else you can cram into an Excel worksheet.<\/P>\n
As long as we\u2019re on the subject, you might find it useful to know that this new spreadsheet is actually opened up as the second workbook in your running instance of Excel. That means it\u2019s possible to use your script to control this new workbook. For example, these two lines of code first create an object reference name objNewWorkbook that binds to the new spreadsheet, then use the SaveAs<\/B> method to save thie file as C:\\Scripts\\New_Workbook.xls: <\/P>
Set objNewWorkbook = objExcel.Workbooks(2)\nobjNewWorkbook.SaveAs(“c:\\scripts\\new_workbook.xls”)\n<\/PRE>\n
That could come in handy.<\/P>\n
In case you\u2019re wondering, TY, it\u2019s only the Scripting Guy who writes this column who is a rabid follower of the University of Washington; in fact, he\u2019s also the only Scripting Guy who attended the UW. But does that somehow make him better than his colleagues, colleagues who attended such diverse universities as Dalhousie (Dean), Western Washington (Jean), and the University of Northern Jupiter (Peter)? Well, now that you mention it \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I copy a worksheet in an existing Excel spreadsheet to a new spreadsheet?— TY Hey, TY. Before we tackle your question we wanted to note that, as we write this, the University of Washington football team has a winning record. Sure, we\u2019re only three weeks into the college football season, […]<\/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-66453","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! How can I copy a worksheet in an existing Excel spreadsheet to a new spreadsheet?— TY Hey, TY. Before we tackle your question we wanted to note that, as we write this, the University of Washington football team has a winning record. Sure, we\u2019re only three weeks into the college football season, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66453","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=66453"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66453\/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=66453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}