Hi,

I've got a workbook (time log) whose base sheets are quite similar. I
need a function that references the current worksheet name and/or
index, similar to how row() and column() work. [See below for why]

With the code below, everything is fine for the current (active)
worksheet. But if I put either function in cells on multiple sheets,
ALL sheets get the value of the currently highlighted (active) sheet!
This will not work.

How do I reference the sheet that contains the cell being
calculated???

Function MySheetIndex() As Integer
MySheetIndex = ActiveSheet.Index
End Function

Function MySheetName(Optional iIndex As Integer) As String
If IsMissing(iIndex) Then
MySheetName = ActiveSheet.Name
Else
MySheetName = ActiveWorkbook.Worksheets(iIndex).Name
End If
End Function


Thanks MUCH!
Pete

PS - More explanation:

The main variation between sheets is the columns of dates, etc.

I want to make it possible to begin each year by copying and pasting a
baseline sheet across all sheets.

To do this, and to simplify a lot of other stuff, the worksheets are
named for each period (e.g. "Jan 1", "Jan 16", "Feb 1" etc).

Given the function above, I can generate date references, etc that are
based on the current sheet name :)


---
Message posted from http://www.ExcelForum.com/