Good evening

I am consolidating a couple of workbooks and am copying / moving a few sheets to new workbooks. Several of these sheets contain formulas that refer to cells in other sheets. I would like these formulas to remain relative to their NEW home, and not be linked to the old workbook.

Example
Workbook1 - Sheet1 - Cell A1
Please Login or Register  to view this content.
When I copy the sheet to a new workbook (Workbook2), the resulting formula is
Please Login or Register  to view this content.
The new Workbook already has a Sheet2 that I would like the formula to refer to. Is there any way to copy the sheet without the resulting messed up link? This occurs even if I select all / copy /paste onto an existing sheet (without the move/copy sheet function)

I am sure there is something simple I am overlooking but it eludes me

Thanks for any help

Lee