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’re only three weeks into the college football season, and the UW’s record is only 2-1, but the way the last two years have gone (3 wins and – it hurts to type this – 19 losses) a 2-1 record and a 21-20 victory over Fresno State is definitely something worth mentioning. So we’re mentioning it.

And, let’s face it: if we’re 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 ….

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.

If you’re 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.

Anyway, the important thing is that the Huskies finally started to – what’s that? Show you how to copy a worksheet from an existing Excel spreadsheet to a brand-new spreadsheet? Well, we’re not sure what that has to do with college football, but, hey, why not:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(“Sheet1”)
objWorksheet.Activate
objWorksheet.Copy()

See, we told you it was easy. The script starts out by creating an instance of the Excel.Application object, and then sets the Visible 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 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 method to make Sheet1 the active worksheet:

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

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’ve connected to Sheet1 and made this the active worksheet all we have to do is call the Copy method:

objWorksheet.Copy()

That’s 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.

As long as we’re 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’s 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 method to save thie file as C:\Scripts\New_Workbook.xls:

Set objNewWorkbook = objExcel.Workbooks(2)
objNewWorkbook.SaveAs(“c:\scripts\new_workbook.xls”)

That could come in handy.

In case you’re wondering, TY, it’s only the Scripting Guy who writes this column who is a rabid follower of the University of Washington; in fact, he’s 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 ….