Copy File Formulas between Files w/o the link

[PJ in CO]

Is there a way to copy a Excel file with just formulas (not data) and apply
it to another file w/o the copied file link included? I have a program which
spits out a xls file for each data set generated and I'd like to manipulate
the data repeatedly for each file w/o having to generate the doggone formulas
each time. Thank you

 

[Gary''s Student]

Let's say we have two workbooks, Book1.xls and Book2.xls. Each has Sheet1.

Here is a trick for high speed. Rather than copying individual cells with
formulas, we copy the entire sheet and then remove the non-formulas. (only a
single copy/paste):

Sub copy_formula()
Set w1 = Workbooks("Book1")
Set w2 = Workbooks("Book2")
w1.Sheets("Sheet1").Cells.Copy w2.Sheets("Sheet1").Range("A1")

w2.Activate
Sheets("Sheet1").Activate

For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
Else
r.Clear
End If
Next

End Sub

 

[Gord Dibben]

One method....

F5>Special>Formulas>OK

Edit>Replace

What: =

With: ^^^

Replace all.

Copy to the new workbook/worksheet and then reverse the process of Edit>Replace.


Gord Dibben MS Excel MVP


On Tue, 11 Dec 2007 15:54:01 -0800, PJ in CO <PJ in

 

[PJ in CO]

One method....

F5>Special>Formulas>OK

Edit>Replace

What: =

With: ^^^

Replace all.

Copy to the new workbook/worksheet and then reverse the process of Edit>Replace.


Gord Dibben MS Excel MVP


On Tue, 11 Dec 2007 15:54:01 -0800, PJ in CO <PJ in


Thank you Both for your reply - I haven't attempted the first method yet, but will when I attempt to automate the number crunching. The second method described worked just fine. Thank you again.