How Can I Add Additional Worksheets to an Excel Workbook?

ScriptingGuy1

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 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, select Worksheet, and then click OK 17 times. Wouldn’t it have been faster and easier to do all that using a script? He’d rather not talk about it. (Editor’s Note: And, no, we can’t tell you which Scripting Guy did this: Greg would be terribly embarrassed if anyone ever found out.)

Fortunately, AA, you don’t have to do things the hard way. Assuming you have Excel configured to include three worksheets with each new workbook, here’s a script that adds nine more worksheets for you:

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

Set objWorkbook = objExcel.Workbooks.Add()
Set colSheets = objWorkbook.Sheets
colSheets.Add ,,9

Yes, you’re right: except for a slightly-cryptic last line this is a pretty simple little script. We begin by creating an instance of the Excel.Application object and then setting the value of the Visible 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’ve changed the default setting, will have three worksheets in it) and create an object reference to the Sheets collection:

Set objWorkbook = objExcel.Workbooks.Add()
Set colSheets = objWorkbook.Sheets

The rest is easy. We want to add nine additional worksheets to the Sheets collection, so we call the Add method, specifying the number of new worksheets (9) as the third parameter:

colSheets.Add ,,9

Cool, huh?

Good question: what about 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’t 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):

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

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
Set colSheets = objWorkbook.Sheets
colSheets.Add ,objWorksheet,9

As you can see, we use this line of code to create an object reference to worksheet 1:

Set objWorksheet = objWorkbook.Worksheets(1)

And then we use that object reference as the second parameter in order to specify that the new worksheets get added after worksheet 1:

colSheets.Add ,objWorksheet,9

That’s all there is to it. To add all the new worksheets directly in front of sheet 3, you’d use code like this, this time using the object reference as the first parameter:

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(3)
Set colSheets = objWorkbook.Sheets
colSheets.Add objWorksheet,,9

One other little note: when you add new worksheets they always get added in “backwards.” That is, suppose you add three new worksheets after worksheet 3. You might expect to see the worksheets arranged in this order:

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6

Nope. Instead, you’ll see this:

Sheet1
Sheet2
Sheet3
Sheet6
Sheet5
Sheet4

We knew you were going to ask that. In answer to your question, take a look at this Hey, Scripting Guy! column to learn how you can sort worksheets within a workbook.

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

But if you’re wondering why someone who calls himself a Scripting Guy didn’t even use a script to save him a bunch of time and trouble, well, we don’t have the answer to that question.

ScriptingGuy1

Follow