Conducting Copy/Paste with absolute formula references

**BenBarrass** · 05-19-2008, 08:06 AM

Hi Guys,

When you cut a region/cell in Excel (in which relative formula references are defined) a subsequent paste of this data performs the equivalent of copying absolute references. When you perform a copy the references are pasted as relative. Works fine on most occasions.

I wish (on many occasions) to leave the original data where it is (and therefore not "cut" it), but on the copy/paste wish excel to perform the equivalent of cut followed by paste. I am surpised this has not become one of the Paste Special or right button options by now. Maybe there is an add-in I need to be made aware of?

My formulea have far too many references for me to F4 ($) every occurance prior to the copy. Generally, I need to perform a standard copy/paste (where relative referenceing is appropriate) immediately afterwards (using the data just copied) and therefore I would to cycle back through using F4 ($) for all these references. I generally have a mix of column/row absolute referencing combined with fully absolute references - keeping track of which is which once they are all fully absolute is a problem.

In the past I have done search and replace for = to # prior to the copy/paste (followed by search & replace # to =), but now the number of occasions I need to do this particular operation means this is quite laborious.

Please could someone suggest an alternative (more automated) solution that may already be out there. I appreciate these are likely to be VBA/Macro solutions.

Thank you in advance,

**Greg M** · 05-19-2008, 10:22 PM

Hi Ben,

The attached workbook might help you out. The method I've used isn't the most elegant, but it seems to work!

When you open the workbook it opens in a hidden window, so the only thing you'll see is a new toolbar with three buttons: Cut Range, Paste Range & Restore Original, and Exit.

Open the workbook which contains the formulas you want to copy.

Select the range you're interested in & press the Cut Range button. This cuts the range, but first makes a copy of the formulas in a temporary workbook.

Select the first cell of the range to which you want to copy the formulas and press the Paste Range & Restore Original button. This moves the original formulas to the new range (so all references are unchanged), and then retrieves the copy of the original formulas from the temporary workbook and pastes them back into their original location. The temporary workbook ("behind" the workbook you're working with) is then closed automatically.

One point - if you cut a range and then change your mind and just press Esc instead of the Paste & Restore button there's no real harm done - the temporary workbook will remain open "in the background", but it will be closed automatically the next time the Cut Range button is pressed.

Hope this helps - please let me know how you get on.

Regards,

Greg M

**BenBarrass** · 05-20-2008, 04:49 AM

Hi Greg,

Thanks. This exactly the sort of thing I was looking for. Although, I haven't had a chance to use it more than a few times as yet, it does do exactly what you say and what I need.

Having looked at the Macro behind the scenes it looks like it could be tailored to do various types of options on the paste.special operation, which could be useful.

This is definitely an improvement over my current situation.

Thanks again,

Ben.

**Greg M** · 05-20-2008, 08:34 AM

Hi Ben,

Many thanks for your feedback - delighted to have helped.

I forgot to mention (although you may already have discovered it yourself) that the application works even if you want to copy/paste the formulas into another worksheet. I never checked to see if it would copy/paste formulas into another workbook - maybe that'll be the MkII version!

Best regards,

Greg M

**Morello172** · 11-18-2009, 05:32 PM

Greg,
I stumbled across this thread when I searched online for a solution to a similar problem as Ben's. You're awesome. Thank you so much. Why the internet is such a great place.

Josh S.