One method to do this is to use the indirect() function when you need to
refer to the previous sheet.
for example
say you call your sheets "week 01" etc

Set up a cell for reference (Say A1)
when you generate the new sheet
change Cell A1 to reflect the old sheet
for example in week 06 enter week 05
put equations such as
=indirect("'"&A1&"'!D3")
if you want to pull in the value from D3 in week 05 sheet

"Shazzer" wrote:

I have a workbook in which a new worksheet is created each week based on a
copy of the previous week. The sheets are named from 1 to 52. Is there any
way I can refer to last week's sheet in a formula. If I was to refer to the
sheet's name I would have to update the formulas each week to refer to the
next number which I would rather avoid doing.

Thank you in advance