Microsoft Office Tutorials and References
In Depth Information
In Figure 133, the active worksheet could be referred to as either Worksheets(2)
or Worksheets("Patient Accounting") . In my VBA books and seminars,
I tell people that it is better to use Worksheets("Patient Accounting") .
However, in some situations, you might really need to refer to a worksheet with
an index number. Perhaps if you needed to refer to a sheet two sheets to the
right of the active sheet, you could refer to Worksheets(x+2) . Is there an
easy way to fi gure out the index number of the active sheet?
You might fi gure it out using a brute-force loop:
Ctr = 1
For each WS in Activeworkbook.Worksheets
If WS.Name = ActiveSheet.Name then Exit For
Ctr = Ctr + 1
Next WS
However, this is the long way around. The Index property of a worksheet
identifi es the location of the worksheet within the workbook. You could use
Worksheets("Patient Accounting") .Index to return the number 2, or
you could simply use ActiveSheet.Index to return the number 2.
Summary: The Index property returns the position of a worksheet in the
Challenge: You type Jan into a cell, grab the fi ll handle, and drag down fi ve
cells. Excel types Marcia , Cindy , Bobby , Greg , and Peter . No, sorry. Excel
types Feb , Mar , Apr, May , Jun . The fi ll handle can do all sorts of amazing fi lls,
handling months, quarters, weekdays, dates, and so on. If you set up a custom
list, Excel can even extend your list of departments, products, or Brady Bunch
kids. The fi ll handle is so useful, wouldn’t it be cool if you could use it to copy
the current worksheet into new worksheets that have appropriate names? For
example, if you have a worksheet named Tuesday and used the fi ll handle, you
could have Excel add new worksheets Wednesday , Thursday , and Friday to
the right of Tuesday .
Solution: Add the following code to your Personal Macro Workbook:
Sub FillHandleSheets()
‘ Make copies of the current worksheet
‘ The new worksheets will have names as if using the Fill
Search JabSto ::

Custom Search