Well i ges that renaming sheet "Week 1" to "Week 2" isent fixing your problem
so try

Sub xChange()
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
For Each c In Selection
c.Formula = Application.WorksheetFunction.Substitute(c.Formula , "Week 1",
"Week 2")
Next
End Sub


"gemma" skrev:

hi i've set uo a work sheet to link to another i.e 'Week 1'!$C$6 , 'Week
1'!$D$6 and so on i want to change the multiple entries on the spread sheet
to pick up the data on "week2" spreadsheet so the forumal will read 'Week
2'!$D$6, is there a easier way to do this without going thru each cell and
manually changing the formula.
thanks