Copy File Formulas between Files w/o the link
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 |
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 |
Copy File Formulas between Files w/o the link
One method....
F5SpecialFormulasOK EditReplace What: = With: ^^^ Replace all. Copy to the new workbook/worksheet and then reverse the process of EditReplace. Gord Dibben MS Excel MVP On Tue, 11 Dec 2007 15:54:01 -0800, PJ in CO <PJ in 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 |
Copy File Formulas between Files w/o the link
"Gord Dibben" wrote: One method.... F5SpecialFormulasOK EditReplace What: = With: ^^^ Replace all. Copy to the new workbook/worksheet and then reverse the process of EditReplace. Gord Dibben MS Excel MVP On Tue, 11 Dec 2007 15:54:01 -0800, PJ in CO <PJ in 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 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. |
All times are GMT +1. The time now is 04:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com