Page 69 - Excel Progamming Weekend Crash Course

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

44 Friday Evening

Copying and Moving Worksheets

An entire worksheet can be copied or moved to a new location within the original workbook
or to a different workbook. The syntax for copying is as follows (use the Move method for
moving a worksheet; the syntax is the same as Copy):
Sheet.Copy(Before, After)
Sheet is a reference to the worksheet to be copied. If you want to copy to a location in
the original workbook, use the Before or After argument (but not both) to specify the
existing worksheet that the copied worksheet is to be placed before or after. For example,
this code copies Sheet1 and places it after Sheet3.
Worksheets(“Sheet1”).Copy After:=Worksheets(“Sheet3”)

When copying within a workbook, the copy is given the name of the original worksheet
with an index, such as “Sheet1 (2).”
To copy a worksheet to another workbook, omit the After and Before arguments. Excel
creates a new workbook and then copies the worksheet to it.

There is no way to directly copy or move a worksheet to an existing workbook.
To do so, you must use the Range object to copy the data to the Windows
Tip Clipboard and then paste the data to the new location. See Session 10 for
details.

REVIEW

Any VBA program that you write will depend on the Excel object model. This session intro-
duced you to this object model and covered some important background material. Among
the things you learned in this session are:

You work with objects through their properties and methods.
Excel uses collections to keep track of multiple object copies.
The object model is organized as a hierarchy with the Application object at the top.
Excel workbooks are represented by Workbook objects in the Workbooks collection.
Methods are provided for creating new and opening existing workbooks, saving and
closing workbooks, and other required tasks.
Worksheets are represented by Sheet objects in the Sheets collection. You can add,
delete, copy, and move worksheets as needed.