Copy Excel worksheet and maintain relative cell reference in formulas

I've found it easier, in many cases, to do the following:

• copy the sheet to a new workbook
• activate the new sheet in the new workbook
• select all (Ctrl+A)
• do a find/replace on
  • find: [WorkbookA.xlsx]!
  • replace: <leave blank>
• replace all

Try using Ctrl + ~ to display the formulas. Then use Ctrl + A to select everything, copy it and then paste it into notepad.

Finally, copy it out of notepad and paste it into your other workbook.

The unsigned answer right below this one is the one that worked for me, with a very slight variation.

1. Create and save a destination spreadsheet.

2. Use "move", "copy", or drag your page with the formulas into the new spreadsheet. This leaves the formulas on the new page pointing to the old worksheet. Then save the new spreadsheet in the same location as the old worksheet.

3. Then go to the Data Tab > click Edit Links. The option won't be active unless there are links in the page.

4. In the dialog that results, select the name of the source file and click "Change Source."

5. From the open-file dialog that appears next, select the name of the new spreadsheet.

Click Close and you're finished.