Copying range of formulas to anotehr workbook

  • Thread starter Thread starter Brad P
  • Start date Start date
B

Brad P

Excel 2007: I have a large range of cells with formulas I want to copy to
another workbook but it only copies values. I can't copy the sheet to the
destination workbook, only to a new workbook. Anyone know how?

Thanks
 
Excel 2007: I have a large range of cells with formulas I want
to copy to another workbook but it only copies values. I can't
copy the sheet to the destination workbook, only to a new
workbook. Anyone know how?
Click to expand...

I know how to do it with VB code. Select the cells with the formulas you
want to copy, then press ALT+F11 to get into the VB editor. Copy/Paste the
following into the window labeled Immediate (if you do not see it, press
CTRL+G to make it appear)...

Workbooks("Example Workbook.xls").Worksheets("Sheet1").Range("C3").Resize( _
Selection.Rows.Count, Selection.Columns.Count).Formula = Selection.Formula

Next, change the name of my destination workbook from my "Example
Workbook.xls" to the actual name of your destination workbook (make sure the
name is encased in quote marks like my example shows) ; also change the name
of the destination worksheet from my example "Sheet1" to the actual name of
your destination worksheet (again, encased in quote marks) and change my
example C3 destination cell address to the cell address you where the copied
formulas will start being copied to. Then, with the text cursor anywhere on
either line of this code, press the Enter Key. That should copy the formulas
from the selected range in you source workbook to the destination workbook.

Rick Rothstein (MVP - Excel)
 
I would suggest that you have two instances of Excel running at the same time
with a book in each instance.

Close one instance and open both books in the remaining.

Now your formulas will copy to source book as formulas, not values.


Gord Dibben MS Excel MVP
 
Gord Dibben said:
I would suggest that you have two instances of Excel running at the same
time
with a book in each instance.

Close one instance and open both books in the remaining.

Now your formulas will copy to source book as formulas, not values.


Gord Dibben MS Excel MVP
Click to expand...

Thats exactly what was happening. I opened up the otehr workbook in the same
instance using Shift-Open. (I have my Excel set to open all files in
different instances.

Thanks
 
Seems like an awful lot of overhead.

Any particular reason why the new instance for each file?


Gord
 
Back
Top