Use "master formula" in multiple workbooks?

feetimo

New Member
Joined
Mar 14, 2010
Messages
18
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
Unless you want to open all 200 workbooks and install a Workbook_Open macro into each one, I would imagine you will need to create a macro and install it in your PERSONAL.XLS file so it is available all the time.

Or put it in a .XLA workbook and install it as an addin...another option.

Anyway, in your macro you can copy the "key formulas" from a template workbook that is always stored in the same place and paste them into the active workbook in the appropriate columns. This macro could be attached to a keyboard shortcut so it would be easy to initiate an "update" on any sheet you open.

========
Another options is to create a macro in your template file where you want to store these formulas, this macro is designed to silently run through all the workbooks in a folder inserting the updated formulas all at once. This might be preferable since you can then use the sheets normally with no further updating until you change something again.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,583
Office Version
2013
Platform
Windows
How many workbooks are involved? Is there only one sheet in each "child" workbook?

ξ
 

feetimo

New Member
Joined
Mar 14, 2010
Messages
18
Unfortunately, no...as each sheet represents a revision of the "recipe" that each workbook is for, some have just one where some have quite a few. However, there is only one sheet in each workbook that represents the active revision. So, going forward, the obsolete revisions could be left alone and only the first tab in each workbook used to move forward with.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Let us know if Ravi's macro works for you, after you make the suggested code adjustments.
 

feetimo

New Member
Joined
Mar 14, 2010
Messages
18
The macro seems to be thrown by the multiple sheets in each workbook. It assumes one sheet, called "Sheet1". A dialog box opens asking which sheet should be used to "Update Values From", but the macro ends up looping at this dialog box.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Try this one, perhaps easier to read and edit:
Code:
Option Explicit

Sub UpdateFormulas()
'JBeaucaire   3/18/2010
'Open all xls files in a folder, update the formulas IN E:G on every sheet
Dim fPath As String, fName As String, LR As Long
Dim NewFormulaRNG As Range, ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'The range of cells that will serve as the master formulas
    Set NewFormulaRNG = ThisWorkbook.Sheets("Sheet1").Range("E2:G2")
'Path to stored files
    fPath = "C:\2010\"
'create a list of files in that folder
    fName = Dir(fPath & "*.xls")

'Loop through files one at a time
    Do While Len(fName) > 0
        'open the file
            Workbooks.Open fPath & fName
        'Copy in the formulas into each sheet
            For Each ws In ActiveWorkbook.Worksheets
                LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                NewFormulaRNG.Copy ws.Range("E2:G" & LR)
            Next ws
        'Save and close the updated file
            ActiveWorkbook.Close True
        'ready the next filename
            fName = Dir
    Loop

Set NewFormulaRNG = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
if the sheet names are same across all files, it can be included in the macro to replace sheet1.
RAvi
 

feetimo

New Member
Joined
Mar 14, 2010
Messages
18
No luck, Ravi...the sheet names are the revision dates, like "3-16-2010", so they are semi-unique...

Jbeaucaire, I'll try the revised macro you posted tomorrow when I get to work.
 

Forum statistics

Threads
1,085,429
Messages
5,383,620
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top