How to copy range without changing formulas?

[nomail1983]

I have a range of cells with formulas that include (and must have)
relative references. I want to copy that range to another area,
retaining the formulas exactly as written.

Is there an easy way to accomplish that?

The only way I know is to copy the "text" of each cell one by one; that
is, activate each cell, copy from the fx field, then paste into the new
cell. That is too tedious for the number of cells involved.

I could write a macro to accomplish the task. But that would take me
more time than the manual copy (albeit less error prone) because I am
not conversant in VBA -- even with the aid of macro recording.

Is there an easier way?

 

[Gord Dibben]

Select the range.

Edit>Replace

what: =

with: ^^^

Replace all.

Copy and paste to new area then reverse the edit>replace steps in both spots.


Gord Dibben MS Excel MVP

 

[Bill Kuunders]

You could do a find and replace.
find =
replace with """""=

and again after copying
find """""=
replace with =

Happy New Year

 

[nomail1983]

Select the range.
Edit>Replace
what: =
with: ^^^
Replace all.
Copy and paste to new area then reverse the edit>replace steps in both spots.

It ain't pretty, but it works great! Thanks.