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?

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

On Wed, 18 May 2011 15:54:08 -0230, "Brad P" <(E-Mail Removed)> wrote:

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

"Gord Dibben" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

Seems like an awful lot of overhead.

Any particular reason why the new instance for each file?


Gord

On Thu, 19 May 2011 08:43:02 -0230, "Brad P" <(E-Mail Removed)> wrote:

>
>"Gord Dibben" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>>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
>