{"id":68323,"date":"2005-12-15T22:06:00","date_gmt":"2005-12-15T22:06:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/12\/15\/how-can-i-add-additional-worksheets-to-an-excel-workbook\/"},"modified":"2005-12-15T22:06:00","modified_gmt":"2005-12-15T22:06:00","slug":"how-can-i-add-additional-worksheets-to-an-excel-workbook","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-add-additional-worksheets-to-an-excel-workbook\/","title":{"rendered":"How Can I Add Additional Worksheets to an Excel Workbook?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I add additional worksheets to an Excel workbook? By default you get 3 worksheets, but I have a scenario in which I need a workbook that has 12 worksheets.

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

Hey, AA. You know, a couple years ago one the Scripting Guys helped put together an Excel spreadsheet for one of the local youth baseball leagues. Because there were 18 teams in the league, he was asked to create a .XLS file with 18 worksheets in it. And because he has Excel configured to use just one worksheet per new workbook (waste not, want not) that meant he had to click Insert<\/B>, select Worksheet<\/B>, and then click OK<\/B> 17 times. Wouldn\u2019t it have been faster and easier to do all that using a script? He\u2019d rather not talk about it. (Editor\u2019s Note: And, no, we can\u2019t tell you which Scripting Guy did this: Greg would be terribly embarrassed if anyone ever found out.)<\/I><\/P>\n

Fortunately, AA, you don\u2019t have to do things the hard way. Assuming you have Excel configured to include three worksheets with each new workbook, here\u2019s a script that adds nine more worksheets for you:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet colSheets = objWorkbook.Sheets<\/p>\n
colSheets.Add ,,9\n<\/PRE>\n
Yes, you\u2019re right: except for a slightly-cryptic last line this is<\/I> a pretty simple little script. We begin by creating an instance of the Excel.Application<\/B> object and then setting the value of the Visible<\/B> property to True; this gives us a running instance of Excel onscreen. We then use these two lines of code to add a new workbook (which, unless you\u2019ve changed the default setting, will have three worksheets in it) and create an object reference to the Sheets<\/B> collection:<\/P>
Set objWorkbook = objExcel.Workbooks.Add()\nSet colSheets = objWorkbook.Sheets\n<\/PRE>\n
The rest is easy. We want to add nine additional worksheets to the Sheets collection, so we call the Add<\/B> method, specifying the number of new worksheets (9) as the third parameter:<\/P>
colSheets.Add ,,9\n<\/PRE>\n
Cool, huh?<\/P>\n
Good question: what about<\/I> parameters 1 and 2? Well, by default new worksheets are added in front of the existing worksheets; that means sheets 1, 2, and 3 (the default worksheets) will actually be pushed to the tail-end of the collection. If you don\u2019t like that you can use the optional parameters 1 and 2 to specify a particular worksheet before which (parameter 1) or after which (parameter 2) all the new sheets will be added. To do that, simply create an object reference to the appropriate worksheet and then use that object reference as the first or second parameter. For example, this script adds the nine new worksheets directly after worksheet 1 (thus pushing sheets 2 and 3 to the end of the collection):<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\nSet colSheets = objWorkbook.Sheets<\/p>\n
colSheets.Add ,objWorksheet,9\n<\/PRE>\n
As you can see, we use this line of code to create an object reference to worksheet 1:<\/P>
Set objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
And then we use that object reference as the second parameter in order to specify that the new worksheets get added after worksheet 1:<\/P>
colSheets.Add ,objWorksheet,9\n<\/PRE>\n
That\u2019s all there is to it. To add all the new worksheets directly in front of sheet 3, you\u2019d use code like this, this time using the object reference as the first parameter:<\/P>
objExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(3)\nSet colSheets = objWorkbook.Sheets<\/p>\n
colSheets.Add objWorksheet,,9\n<\/PRE>\n
One other little note: when you add new worksheets they always get added in \u201cbackwards.\u201d That is, suppose you add three new worksheets after worksheet 3. You might expect to see the worksheets arranged in this order:<\/P>
Sheet1\nSheet2\nSheet3\nSheet4\nSheet5\nSheet6\n<\/PRE>\n
Nope. Instead, you\u2019ll see this:<\/P>
Sheet1\nSheet2\nSheet3\nSheet6\nSheet5\nSheet4\n<\/PRE>\n
We knew<\/I> you were going to ask that. In answer to your question, take a look at this<\/B> Hey, Scripting Guy!<\/I><\/B><\/A> column to learn how you can sort worksheets within a workbook.<\/P>\n
And you\u2019re right, you could also reconfigure Excel to give you 12 worksheets in every new workbook by default. For more information on that, see The Picture Book of Microsoft Office Management<\/B><\/A>. <\/P>\n
But if you\u2019re wondering why someone who calls himself a Scripting Guy didn\u2019t even use a script to save him a bunch of time and trouble, well, we don\u2019t have the answer to that<\/I> question.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I add additional worksheets to an Excel workbook? By default you get 3 worksheets, but I have a scenario in which I need a workbook that has 12 worksheets.— AA Hey, AA. You know, a couple years ago one the Scripting Guys helped put together an Excel spreadsheet for one […]<\/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":[710,48,49,3,5],"class_list":["post-68323","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I add additional worksheets to an Excel workbook? By default you get 3 worksheets, but I have a scenario in which I need a workbook that has 12 worksheets.— AA Hey, AA. You know, a couple years ago one the Scripting Guys helped put together an Excel spreadsheet for one […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68323","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=68323"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68323\/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=68323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=68323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=68323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}