Sheets Collection Object

Description

A collection of all the sheets in the specified or active workbook. The Sheets collection can contain Chart, DialogSheet, Module, or Worksheet objects.

The Sheets collection is useful when you want to access sheets of any type. If you need to work with sheets of only one type, see the object topic for that sheet type.

Accessors

Use the Sheets method with the sheet index number or name as an argument to access a single member of the collection or without an argument to access the entire collection at once.

The index number represents the position of the sheet on the tab bar of the workbook. Sheets(1) is the first (leftmost) sheet in the workbook; Sheets(Sheets.Count) is the last sheet. All sheets are included in the index count, even if they are hidden.

The following example hides the sheet one in the active workbook.


Sheets(1).Visible = False

The sheet name is shown on the workbook tab for the sheet. Use the Name property to set or return the sheet name (you cannot change the name of the sheet where the Visual Basic code is running, however). The following example protects the Visual Basic code on the sheet named "Sample Code."


Sheets("sample code").Protect password:="drowssap", contents:=True

You can also use an array to specify more than one sheet. The following example moves the sheets named "Module1" and "Sheet1" to the beginning of the workbook.


Sheets(Array("module1", "sheet1")).Move before:=Sheets(1)

Use the Add method to create a new sheet and add it to the collection. The following example adds two dialog sheets to the active workbook, placing them after sheet two in the workbook.


Sheets.Add type:=xlDialogSheet, count:=2, after:=Sheets(2)

Properties

Application Property, Count Property, Creator Property, Parent Property, Visible Property.

Methods

Add Method (Sheets Collection), Copy Method, Delete Method, FillAcrossSheets Method, Item Method, Move Method, PrintOut Method, PrintPreview Method, Select Method.