Mac – Copy Excel worksheet and maintain relative cell reference in formulas

macrosmicrosoft excelmicrosoft-excel-2007microsoft-excel-2010vba

Another copy problem in Excel:

How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[WorkbookA.xlsx]!B!23 when copied over.

I want to maintain "relative" cell references instead of "absolute" cell references (i shall invent this terminology in Excel world if it does not yet exists).

Another possible alternative that I cannot get it to work is the option to paste cell "values" only. Excel treats "values" as calculated values rather than the actual formulas in the cell. If I choose paste formula, it still gives absolute references.

More About Why I Need This:
I have a production xlsx in use for daily operations. We constantly need to make "upgrades" to this xlsx and so one person may create a copy and his changes there for a single sheet. Concurrently, another person may also be making changes to another sheet. Given that these sheets have no dependant cells on other sheets, like a summary report, it is desirable for us to just copy and merge the sheets back into the original xlsx. But the "absolute" referencing is giving a lot of trouble.

Best Answer

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.