Microsoft Office Tutorials and References
In Depth Information
The Sheets and Worksheets Collections
When most users think of a “sheet” in a workbook, they think of a worksheet, with rows,
columns, cells, data, and formulas. However, there are several types of sheets you can have in a
workbook. There is the worksheet, of course, but there is also a chart sheet . It’s a bit confusing
that a worksheet can contain a chart, but when you go through the Chart Wizard, you get the
option to put the chart on a separate chart sheet. The other two types of sheets are meant to
handle Excel 4 macros; there is one sheet for U.S. macros and another for international macros.
The Sheets and Worksheets collections have a number of properties in common, but there are
a few things you have to watch out for when you work with every sheet in a workbook. Those
issues are most pronounced with regard to the Count property, which is the first property
you’ll encounter in this section.
The Count property of the Wo rksheets collection looks through the named workbook and
counts the number of worksheets in the workbook, while the Count property of the Sheets
collection reflects the combined number of chart sheets and worksheets in your workbook.
You can use the Count property of the Sheets and Wo rksheets collections to check your
workbooks’ structure for accuracy before you pass the workbook to another procedure for
Do While Worksheets.Count < 12
You’ll find the remainder of this procedure below in the discussion of the Wo rkbook object’s
Part of a sheet’s public face is its name, which is how the sheet is identified on the tab bar and
one way you can identify the sheet in your VBA code. If you want to change the name of a
worksheet, you can do that by setting the Name property. For example, if you copy the weekly
sales totals to a worksheet at the end of a workbook, you can change the name of that
worksheet to Summary using this procedure:
Dim strWkshtName As String
strWkshtName = "Summary"
Sheets(Sheets.Count).Name = strWkshtName