Use "master formula" in multiple workbooks?

[feetimo]

I have multiple workbooks (.xls files) saved in a folder that each represent a seperate "recipe" or machine setup. Each workbook may contain several worksheets that are revisions of the given recipe. The format of the worksheets is as follows (roughly):

Columns A-D: User inputs
Columns E-G: Computer-generated outputs using formulas on the inputs.
...each worksheet containing approximately 200 rows of input/output data.

Currently the formulas are just that...formulas in each cell of each worksheet and workbook. A mess when mistakes/updates are needed for the formulas!

My questions:
1) Is there a way to keep the formulas in a separate master file/ worksheet/even macro if need be...remember, though, this is across worksheets AND workbooks (different .XLS files)--So that if a change is needed to the column "E" formula, for example, that it will be reflected in all workbooks as they are opened.
2) Seeing as the output cells will be transferred to an external system via .CSV or similar, is there a better application for the job?

 

[jbeaucaire]

I trust you will read through the code and make any corrections needed before trying it, like to the fPath variable, yes?

 

[feetimo]

It works! What's the required code to except a specific worksheet...all my workbooks have a "LOOKUP" sheet that contains lookup-type information. It would be good if the formula left this sheet alone.

 

[jbeaucaire]

Add this and edit to suit:

        'Copy in the formulas into each sheet
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> "Lookup" Then
                    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                    NewFormulaRNG.Copy ws.Range("E2:G" & LR)
                End If
            Next ws