Programming Excel with VBA and .NET

Name

workbook.Sheets

Synopsis

Returns a collection of all the sheets in a workbook. There is no “Sheet” object type, so the Sheets collection returns a varied collection of objects that may include Worksheet, Chart, and DialogSheet objects.

Tip

DialogSheet objects are now considered obsolete by Microsoft and are no longer documented. The same applies to the xlExcel4MacroSheet and xlExcel4IntlMacroSheet subtypes of the Worksheet object. However, Excel still supports their creation and you may encounter them when working with the Sheets collection.

In general, it is a good idea to use specific types for objects if at all possible. For instance, the Worksheets collection returns the collection of Worksheet objects in the workbook, and the Charts collection returns the collection of Chart sheet objects in the workbook.

The Sheets collection is most useful when you want to work with general aspects that apply to all sheets, such as their order in a workbook. For instance, this code moves the currently active sheet to be the first one in the active workbook:

ActiveSheet.Move Sheets(1)

In the preceding case, it doesn’t matter what type of sheet it is—the workbook is reordered.