Microsoft Office Tutorials and References
In Depth Information
Cells and Ranges
This one-line macro will add a new worksheet, name it as you specify, and place it at the far right
end of the worksheets, which is the highest worksheet index number based on the count of existing
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = “Test1”
You can place a worksheet relative to another worksheet’s name, this time adding a worksheet, and
placing it before Sheet2:
Worksheets.Add(Before:=Worksheets(“Sheet2”)).Name = “Test2”
The preceding examples will work without any problem, as long as the
workbook does not already contain a worksheet with the name Test1 or Test2. Excel
does not allow worksheets to be given duplicate names in the same workbook,
and attempting to do so will result in an error. You’ll learn about handling VBA
errors in Lesson 17.
You may want to relocate an existing worksheet from its current position to a particular index
position for the convenience of your workbook’s users. Suppose that during the course of your macro, you
want the active worksheet to occupy the number two worksheet index position — that is, to be the
worksheet that is located second from the left as you see the worksheet tabs. To accomplish this, you
can place the active worksheet after the first index worksheet, as shown in the following example.
A word of caution about the Worksheets collection: there is a difference between
the Sheets collection and the Worksheets collection. You probably know about
Chart sheets, and if your workbook has one, you need to be mindful to cycle
through the Worksheets collection only if you are interested in manipulating
worksheets. If you cycle through the Sheets collection, all sheets, including a Chart
sheet (or outmoded Dialog Sheets or Macro sheets) will be included in the
procedure. If you only want to act on worksheets, specify the Worksheets collection.
cElls And rAngEs
The Range object is probably the most utilized object in VBA. A range can be a single cell or a range
of cells that spans any size area. A Range object, then, is a cell or block of cells that is contained on
a Worksheet object. Though a Range object can be a union of several noncontiguous blocks of cells,
it is always the case that a VBA Range object is contained on one worksheet. There is no such thing
as a Range object that includes cells on different worksheets.