How do I copy and paste formulas between workbooks?

T

tezza

I have 2 versions of a workbook. I've identified an error in some of the
formulas in one and having corrected them, I want to copy and paste into the
other, but it only pastes the values. Any ideas?
 
T

tezza

Thanks. I was having problems because I had 2 instances of Excel open and it
wont perform 'paste special-formulas' between the 2. However, now when I
paste the formulas they refer back to the original workbook. I suspect I will
have to use the replace function to get rid of all references?
 
J

JLatham

You can try copying from the formula bar instead of copying the cell. Choose
a cell, highlight the formula in the formula bar and [Ctrl]+[C] to copy it.
Then I find it's better for some reason to hit [Esc] to get out of copy mode
and go to the other workbook, choose the cell where you want the formula to
be and paste it in there through the formula bar also. This will make a
literal copy without workbook references. Disadvantage is having to do it
one formula at a time.
 
G

Gord Dibben

Select the cells to copy.

Edit>Replace

What: =

With: xxx

Replace all.

Copy these over to new workbook.

Reverse the edit>replace on new workbook.

Close source wookbook without saving.


Gord Dibben MS Excel MVP

You can try copying from the formula bar instead of copying the cell. Choose
a cell, highlight the formula in the formula bar and [Ctrl]+[C] to copy it.
Then I find it's better for some reason to hit [Esc] to get out of copy mode
and go to the other workbook, choose the cell where you want the formula to
be and paste it in there through the formula bar also. This will make a
literal copy without workbook references. Disadvantage is having to do it
one formula at a time.

tezza said:
Thanks. I was having problems because I had 2 instances of Excel open and it
wont perform 'paste special-formulas' between the 2. However, now when I
paste the formulas they refer back to the original workbook. I suspect I will
have to use the replace function to get rid of all references?
Click to expand...
Click to expand...
 
Top