Copying range of formulas to anotehr workbook

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
 
R

Rick Rothstein

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?

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)
 
G

Gord Dibben

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
 
B

Brad P

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

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
 
G

Gord Dibben

Seems like an awful lot of overhead.

Any particular reason why the new instance for each file?


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top