Page 177 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

ThisWorkbook.Sheets.Add Before:=Worksheets(1)
ThisWorkbook.Sheets.Add Before:=Sheet1
Chapter 7
ThisWorkbook.Sheets.Add Before:=ActiveSheet
The last statement does assume that Sheet1 is the active sheet.

Note Unlike normal arrays, which start with an index value of 0 by default, the Sheets and
Worksheets collections begin with an index value of 1. Go figure.

If you want to add more than one worksheet to a workbook, the Visual Basic Editor help sys-
tem indicates you can set the Count parameter to reflect the number of sheets you want to
add. For example, if you wanted to add three sheets after a sheet named March, you would
use the following command:

ThisWorkbook.Sheets.Add After:=March, Count:=3
The last parameter you can use with the Add method is the Type parameter, which determines
the type of sheet that’s added to the workbook. You can use the following four constants:
● xlWorksheet, which adds a worksheet (the default)
● xlChart, which adds a chart
● xlExcel4MacroSheet, which adds an Excel 4 macro sheet
● xlExcel4IntMacroSheet, which adds an international Excel 4 macro sheet
The xlExcel4MacroSheet and xlExcel4IntMacroSheet constants are included to ensure Excel
2003 is backward compatible with Excel 4.

You might have noticed that there’s no direct way to add a sheet to the end of a workbook;
however, you can use the Count property of the Sheets or (if there are no other types of sheets
in your workbook) Worksheets collection to determine how many sheets are in the workbook
and add the worksheet after it. The following code shows one way to do just that:

ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)

Tip Start with the Right Number of Sheets
If you know how many total worksheets you will need in a given workbook, you should prob;
ably create the workbook with the required number of worksheets rather than adding them
later. You should create the workbook with the proper number of worksheets so that you
avoid any problems accessing the workbook, whether the difficulties are from another user
having the workbook open or a network problem if you’re trying to change a workbook on
another computer.

151