replace formula references with previous sheet names
 

Hi,

I have a workbook with a macro that copies my template sheet, inserts
it as a new sheet at the end of all the sheets, and renames the new
sheet to Week x+1, one higher than the sheet before.

I have several cells that refer to the previous sheet, using INDIRECT
and PrevSheet:
Function PrevSheet()
'=INDIRECT("'"&PrevSheet()&"'!D48")
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

The problem with this is that I add rows to my sheets, which means that
the information I'm targeting is no longer in D48, and the INDIRECT
reference doesn't change.

I was wondering if, on my template sheet, I can replace
=INDIRECT("'"&PrevSheet()&"'!D48") with =?D48 and then have a macro
search out the name of the previous sheet, and replace all the ? on my
new sheet with that name. So after the macro has run, the cell in
question will now read ='Week 24 2006'!D54 (or equivalent). Hopefully,
that reference will then change as I add rows to the sheet called Week
24 2006.

Any suggestions?

Thanks for any help you can give,
Helen