Take a look at this function. It returns the value from cell A1 of the next
sheet.

Public Function Test() As Variant
Application.Volatile

Test = Application.Caller.Parent.Next.Cells(1, 1)

End Function

If there is no next sheet then it returns #Value
--
HTH...

Jim Thomlinson


"w_c_mead" wrote:

It might be useful to back up and pose the problem I'm trying to solve. I'd
like to be able to use the sheet number (index) of a sheet as a function
argument so that the function can refer to cells using a
Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify
nsheet explicitly. But that is not general enough to survive if a new sheet
is added to the workbook.

I can't find a property or method that allows me to set nsheet to the
current sheet number so that I could offset it to refer to the previous or
next sheet.

I've got a kluge that works if I store the sheet number in a cell on each
sheet, but this value has to be reset by a "renumber" operation if a sheet is
inserted. Yuk!

How would you go about this?