How do I copy formulas which read from several sheets within a workbook into
another workbook? Currently, as I copy from one book to another, Excel wants
to link the two books. The books have identical sheet names/page design.

Example:
Copy from:
MyFile.xls cell A1 formula
=Jan!C5+Feb!C5+Mar!C5+Apr!C5

Paste to:
Newfile.xls Cell A1 becomes
='[MyFile.xls]Jan'!C5+'[MyFile.xls]Feb'!C5+'[MyFile.xls]Mar'!C5+'[MyFile.xls]Apr'!C5

Can I copy without linking?

Thanks in advance!

I think you are looking for something like:

Workbooks("MyFile").Sheets(???).Range("A1").copy _
Destination:=Workbooks("Newfile").Sheets(???).Rang e("A1")

You can make this more generic:

===================
Sub xfer()
Dim rw As Integer, col As Integer, wksh As Integer

For wksh = 1 To Workbooks("MyFile").Worksheets.Count ' cycle through all
worksheets
For rw = 1 To
Workbooks("MyFile").Sheets(wksh).Cells.SpecialCell s(xlLastCell).Row '
cycle through all used rows
For col = 1 To
Workbooks("MyFile").Sheets(wksh).Cells.SpecialCell s(xlLastCell).Column '
cycle through all used columns
If Len(Workbooks("MyFile").Sheets(wksh).Cells(rw, col)) 0
Then ' ignor empty cells
Workbooks("NewFile").Sheets(wksh).Cells(rw, col).FormulaR1C1
= Workbooks("MyFile").Sheets(wksh).Cells(rw, col).FormulaR1C1
End If
Next
Next
Next

End Sub
========================


You should turn off screenupdating and calculation at the beginning and turn
them back on at the end.

Note that there is no select or activate.

rand451

"TOMB" wrote in message
...
How do I copy formulas which read from several sheets within a workbook
into
another workbook? Currently, as I copy from one book to another, Excel
wants
to link the two books. The books have identical sheet names/page design.

Example:
Copy from:
MyFile.xls cell A1 formula
=Jan!C5+Feb!C5+Mar!C5+Apr!C5

Paste to:
Newfile.xls Cell A1 becomes:
='[MyFile.xls]Jan'!C5+'[MyFile.xls]Feb'!C5+'[MyFile.xls]Mar'!C5+'[MyFile.xls]Apr'!C5

Can I copy without linking?

Thanks in advance!

Manually?

I just change my formulas to text, copy the strings, paste, then change my text
strings back to formulas:

Select all the cells (ctrl-a, twice in xl2003)
edit|replace
what: =
with: $$$$$= (some unique string)
replace all

Copy and paste

Select all
edit|Replace
what: $$$$$=
with: =
replace all

(don't forget to fix it in both workbooks--or close without saving the "sending"
workbook.)

TOMB wrote:

How do I copy formulas which read from several sheets within a workbook into
another workbook? Currently, as I copy from one book to another, Excel wants
to link the two books. The books have identical sheet names/page design.

Example:
Copy from:
MyFile.xls cell A1 formula
=Jan!C5+Feb!C5+Mar!C5+Apr!C5

Paste to:
Newfile.xls Cell A1 becomes:
='[MyFile.xls]Jan'!C5+'[MyFile.xls]Feb'!C5+'[MyFile.xls]Mar'!C5+'[MyFile.xls]Apr'!C5

Can I copy without linking?

Thanks in advance!

--

Dave Peterson