Page 67 - Excel Progamming Weekend Crash Course

d540629 ch03.qxd 9/2/03 9:27 AM Page 42

42 Friday Evening

The arguments are all optional:

Before. This is a reference to the existing worksheet before which the new sheet is
to be added.
After. This is a reference to the existing worksheet after which the new sheet is to
be added.
Count. This argument designates the number of new worksheets to add. The default
is 1.
Clearly, you would not use the Before and After arguments at the same time. If neither
of these arguments is included, the new sheet is inserted before the currently active work-
sheet. Note that the Add method returns a reference to the newly added sheet. If Count is
more than 1, the reference is to the last added worksheet.

You can also add a new worksheet by calling the Sheets collection’s Add
method, but there is no advantage to doing so.
Tip
After creating a new worksheet, it is a good idea to set its Name property to something
other than the default Sheet1 and Sheet2 names assigned by Excel. This name is displayed
on the worksheet’s tab on the Excel screen and can also be used to retrieve a reference from
the Worksheets or Sheets collection.

Dim MyNewWorksheet As Worksheet
Set MyNewWorksheet = ActiveWorkbook.Add
MyNewWorksheet.Name = “Sales Totals”

A worksheet that you add in code is by default hidden. If you need to make
it visible so the user can work with it, set its Visible property to True.
Note
To delete a worksheet, call the Worksheet object’s Delete method:

SomeWorksheet.Delete
Remember that you can also get a reference to a worksheet from the Worksheets
collection:

SomeWorkbook.Worksheets(SheetName).Delete
When code tries to delete a worksheet, Excel normally displays a prompt to the user ask-
ing him or her to confirm the delete action. If you want to delete a worksheet without this
prompt being displayed, you must set the Application object’s DisplayAlerts property to
False:

DisplayAlerts = False
SomeWorksheet.Delete
DisplayAlerts = True