Prevsheet not working in all workbooks

[stevenkimbell]

I inserted a Prevsheet function into VB and saved it to my personal workbook. I wanted it available to all workbooks later. Initially it would not run in any workbook - i had to try several different versions until one finally worked in budget spredsheet. I use it to carry totals over from one week to the next, adding them to the current week's totals. However, I have some extreme difficulty getting it to work in other workbooks. Moreover, when I select Developer - Macros, it never shows up, even in the budget workbook where it functions. I select the current workbook, the personal, all open workbooks - nowhere do I see it.
I am now creating a new workbook using data from one month to another and need the function. I persistently get an error of #Name ?. I tried opening VB while actively in this workbook and copying the Prevsheet module into it - NoGo. I tried creating it as a new entry - again NoGo. I tried using two alternate versions, Same Old, Same Old.
I am stumped.
I have looked at Options - Trust Center to ensure macros aren't disabled. I even tried copying a sheet from the functioning workbook - my budget - into the new workbook. Suggestions?
Below is the current macro in use, the one that works in the budget.

Function Prevsheet(rCell As Range)
Application.Volatile
Dim i As Integer
i = rCell.Cells(1).Parent.Index
Prevsheet = Sheets(i - 1).Range(rCell.Address)
End Function

I also tried a simpler version that seems to work for some people:
Function PrevSheet(rng As Range)
Application.Volatile
PrevSheet = rng.Parent.previous.Range(rg.Address)
End Function

 

[pbornemeier]

Regarding "Moreover, when I select Developer - Macros, it never shows up": Subs and Functions with parameters do not show in the macro window (Alt-F8).
If you run Prevsheet on the leftmost worksheet, you get a "Subscript out of range error" since the formula is attempting to evaluate Worksheet(0) which does not exist
This site http://www.mcgimpsey.com/excel/udfs/prevsheet.html has a more robust version of the macro. Which I include below:

   '******************************************************
   'Purpose: Reference cell(s) on the previous sheet
   'Inputs:  rRng - optional range reference
   'Returns: Range with same address as rRng on the sheet
   '         to the left of the calling sheet. If rRng not
   '         provided, the calling cell reference is used
   '******************************************************
   Public Function PrevSheet( _
            Optional rRng As Excel.Range) As Variant
     Dim nIndex As Integer
     Application.Volatile
     If rRng Is Nothing Then Set rRng = Application.Caller
     nIndex = rRng.Parent.Index
     If nIndex > 1 Then
        Set PrevSheet = Sheets(nIndex- 1).Range(rRng.Address)
     Else
        PrevSheet = CVErr(xlErrRef)
     End If
   End Function

My understanding is the many references to a volatile User Defined Function can cause workbook recalculation to be very slow.

You could put the name of the previous worksheet in the same cell on each worksheet and then use INDIRECT formulas including that cell to eliminate using the volatile function.

If AA47 contains the previous sheet name then
=INDIRECT(AA47 & "!B25",TRUE)
would refer to B25 on that sheet

 

[stevenkimbell]

""You could put the name of the previous worksheet in the same cell on each worksheet and then use INDIRECT formulas including that cell to eliminate using the volatile function.

If AA47 contains the previous sheet name then
=INDIRECT(AA47 & "!B25",TRUE)
would refer to B25 on that sheet[/QUOTE]""

Phil,
Thanks for this bit of advice. I do have some issues with horsepower when I use Prevsheet across large workbooks. I am not yet sure of how to do this, but figuring it out is half the fun.
Thanks again!