How to copy range without changing formulas?
 

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?

How to copy range without changing formulas?
 

Select the range.

EditReplace

what: =

with: ^^^

Replace all.

Copy and paste to new area then reverse the editreplace steps in both spots.


Gord Dibben MS Excel MVP

On 31 Dec 2006 11:20:15 -0800, wrote:

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?

How to copy range without changing formulas?
 

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

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

Happy New Year
wrote in message
ups.com...
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?

How to copy range without changing formulas?
 

Gord Dibben wrote:
Select the range.
EditReplace
what: =
with: ^^^
Replace all.
Copy and paste to new area then reverse the editreplace steps in both spots.

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