Help me make a function dependent on current sheet?!?

[pholzmann]

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

 

[Jim Rech]

I think you should always pass a cell on the sheet whose name you want
returned:

Function ShtName(Rg As Range) As String
ShtName = Rg.Parent.Name
End Function


In a cell:

=ShtName(A1)

 

[pholzmann]

Yahoo!!!!!!

I think you must have a lot of experience ...

That worked great, and it makes sense. I had to adjust our design a
bit, but it is actually better this way...

Thanks MUCH,
Pete

 

[Jim Rech]

I think you must have a lot of experience ...

You saying I'm old? Well,... actually you're right<g>.