Copy File Formulas between Files w/o the link
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
--
Gary''s Student - gsnu200761
"PJ in CO" wrote:
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
|