Copy worksheets to new workbook without formulas referencing the old workbook
Posted on 2016-10-04
I can't believe I'm not seeing the immediate solution to this, but I'm obviously not.
I'm copying two sheets to a new workbook. When I do this the formulas are referencing the original workbook. I do not want this. The formulas only reference things that are copied over already (other cells on same tab or other tab that is being copied simultaneously).
How do I either copy without the file references while keeping my cell references or how do I rid myself of the file portion of the formulas while keeping the rest? Adding code to my current macro is an acceptable solution.
PS - find and replace is a fail because it want't to point to another workbook. I want to lose all file references, not change them.
This in original file =SUM(Annual!C10:C25)
in the new workbook becomes =SUM([Budget.xls]Annual!C10:C25)
I want it back to the original =SUM(Annual!C10:C25)