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.
The solution, of course, is to make sure that all the references within the selection are absolute before doing the copy and paste. Formula, _ xl A1, , xl Absolute) Next c Application. Formula, _ xl A1, , xl Relative, c) Next c Application.
Making the changes to the formulas by hand is tedious. Screen Updating = True End Sub Once this macro is run, you can copy and paste the selection without Excel doing any updating to references. Screen Updating = True End Sub This macro will change all formulas in the selected range to their relative equivalent.
I know there are some paid add-ons that do that but I'm not looking to give money to people for something this simple.
But the "absolute" referencing is giving a lot of trouble. You want to copy sheet 1 from WBA into WBB and you want it to copy the formulas relative to WBB rather than WBA. For me, it was always "the last sheet" but one was named 'Min Flow' and the other was named 'Normal Flow'.
This isn't possible unless WBB sheet 2 is identical to WBA sheet 2. Also, if you just want values you can copy the sheet to have values only. I renamed both to 'Results' and the copy/paste worked as I wanted it to - a "relative paste". Now try to move them by drag n drop to different worksheet (different tab). Here is the tricky part: just press cmd (mac) or alt (win) and it will let you drop the cells to another tab. In the source workbook (Workbook A.xlsx), select the sheet that you want to copy.
So far I have discussed what Excel does with the references in the selection being copied or moved.
What about references to the information in the selection?
(Other than going into every single one and pressing enter) ... Or do you want X1 to have the same exact formula showing in A1?