Page 179 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

Sub DeleteSheet()
Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True Chapter 7
End Sub

Move and Copy Methods
Another handy skill when manipulating workbooks with VBA code is to change the position
of sheets in a workbook, or to move or copy a sheet to a new workbook. One example of
when moving sheets within a workbook would be handy is when you are creating a work-
book that will be used in an annual report or another type of presentation. Because those
types of reports often have strict formatting guidelines, you might end up moving sheets
around to fit the format. You also might need to move or copy a sheet from a workbook into
a summary workbook. For example, if a project manager summarized labor and equipment
expenditures on a sheet with a known name, you could copy that sheet into a target work-
book and use that sheet, not the original, for any processing you needed to do.
Whether you move or copy a sheet depends on whether or not you want to keep the original
sheet. Copying a sheet is a nondestructive operation in that the sheet being copied isn’t
deleted, but moving a sheet does cause the sheet to be deleted from its original location.

The Move and Copy methods let you set the target position of your moved worksheet using
either the Before or the After parameter. (If for some reason you try to use both, you’ll get an
“expected end of line” error.) For example, the following procedure counts the number of
sheets in the existing workbook and moves the sheet named Summary to the end of the
workbook:

Sub MoveToEnd()
Sheets("Summary").Move After:=Sheets(Sheets.Count)
End Sub
If you want to move or copy more than one sheet in a workbook, you can specify an array in
the Sheets collection. For example, the following procedure moves the sheets named Summary
and PivotTable to the beginning of the workbook:

Sheets(Array("Summary", "PivotTable")).Move Before:=Sheets(1)

If you move or copy a sheet without specifying a Before or After parameter, Excel will create a
new workbook where the copied sheet is the only sheet in the workbook, regardless of the
default number of worksheets included in new workbooks.

PageSetup Object
Regardless of whether you work in Excel through the user interface or using VBA code, the
one thing you need to remember is that the data in your workbooks is the most important
element of your workbook. Once you’re sure the data is entered and summarized correctly,
you can focus on how it looks. You’ll learn a lot more about formatting data and worksheets

153