Copy/Paste Formulas Without Linking Books

[Guest]

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!

 

[STEVE BELL]

I think you are looking for something like:

Workbooks("MyFile").Sheets(???).Range("A1").copy _
Destination:=Workbooks("Newfile").Sheets(???).Range("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.SpecialCells(xlLastCell).Row '
cycle through all used rows
For col = 1 To
Workbooks("MyFile").Sheets(wksh).Cells.SpecialCells(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

 

[Dave Peterson]

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

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!