Microsoft Office 2000/Visual Basic Programmer's Guide

Adding, Deleting, Copying, and Moving a Worksheet Object

You can add one or more worksheets to the Worksheets collection by using the collection's Add method. The Add method returns the new Worksheet object. If you add multiple worksheets, the Add method returns the last worksheet added to the Worksheets collection. If the Before or After arguments of the Add method are omitted, the new worksheet is added before the currently active worksheet. The following example adds a new worksheet before the active worksheet in the current collection of worksheets:

Dim wksNewSheet As Excel.Worksheet

Set wksNewSheet = Worksheets.Add
With wksNewSheet
   ' Work with properties and methods of the
   ' new worksheet here.
End With

You use the Worksheet object's Delete method to delete a worksheet from the Worksheets collection. When you try to programmatically delete a worksheet, Excel will display a message (alert); to suppress the message, you must set the Application object's DisplayAlerts property to False, as illustrated in the following example:

Function DeleteWorksheet(strSheetName As String) As Boolean
   On Error Resume Next
   
   Application.DisplayAlerts = False
   ActiveWorkbook.Worksheets(strSheetName).Delete
   Application.DisplayAlerts = True
   ' Return True if no error occurred;
   ' otherwise return False.
   DeleteWorksheet = Not CBool(Err.Number)
End Function

The DeleteWorksheet procedure is available in the modExcelSamples module in ExcelSamples.xls in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

Important   When you set the DisplayAlerts property to False, always set it back to True before your procedure has finished executing, as shown in the preceding example.

You can copy a worksheet by using the Worksheet object's Copy method. To copy a worksheet to the same workbook as the source worksheet, you must specify either the Before or After argument of the Copy method. You move a worksheet by using the Worksheet object's Move method. For example:

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
Worksheets("Sheet1").Move After:=Worksheets("Sheet3")

The next example illustrates how to move a worksheet so that it is the last worksheet in a workbook:

Worksheets("Sheet1").Move After:=Worksheets(Worksheets.Count)

Important   When you use either the Copy or the Move method, if you do not specify the Before or After argument, Excel creates a new workbook and copies the specified worksheet to it.