Template changes to existing workbooks

[farmkid21]

I use an Excel 2003 template for financial statements of my customers.
I have had to make some minor changes to the original template. So ho
do I make the changes to all of my existing workbooks without having t
reenter all the information in the new template?

I've tried to copy and paste, but that brings the old formulas over a
well.

Is there a way to select only the unprotected cells and copy those int
the new template? The cell locations have not changed only the formula
to figure the financial ratios have changed.

I'm not familar with VBA at all. So if that's my only option pleas
explain the steps in layman's terms.

Thank you

 

[arno]

hi,

just an idea:

you can record a macro and save it to your personal macro file (you
will be asked when recording) and assign a shortcut like ctrl+a. then
you make all the changes to one file and stop recording when your are
finished. then you could open all the files one after each other and
press ctrl+a, save and close the file and open the next one.

when you change your template this cannot effect the files that have
been created using the template.

arno

 

[Bernie Deitrick]

farmkid21,

You could use a macro. If the sheet names are the same, you could try something like this, where
you have two workbooks open: the workbook with the new formulas (and with this code), and the
workbook that needs to be updated (which is open and the currently active workbook). Run the sub
Update, below.

This assumes that no cells have been moved, added, removed - just formulas changed, and the sheets
haven't been messed with.

HTH,
Bernie
MS Excel MVP


Sub Update()
Dim mySht As Worksheet
Dim myCell As Range
Dim newWB As Workbook
Dim oldWB As Workbook
Dim myCalc As Variant

Set oldWB = ActiveWorkbook
Set newWB = ThisWorkbook

With Application
.EnableEvents = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each mySht In newWB.Worksheets
For Each myCell In mySht.Cells.SpecialCells(xlCellTypeFormulas)
myCell.Copy oldWB.Worksheets(mySht.Name).Range(myCell.Address)
Next myCell
Next mySht

With Application
.EnableEvents = True
.Calculation = myCalc
.ScreenUpdating = True
End With

End Sub