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.
select all - copy - paste special - values
to have values only. If you want to do it all during the copy rather than rely on the end user to perform those steps, well, I've been working on that. Let us know exactly how you want it to go in terms of WorkbookA (or 1) and WBB(2) and the sheet #s