Template changes to existing workbooks
 

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 how
do I make the changes to all of my existing workbooks without having to
reenter all the information in the new template?

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

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

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

Thank you.


--
farmkid21
------------------------------------------------------------------------
farmkid21's Profile: http://www.excelforum.com/member.php...o&userid=32170
View this thread: http://www.excelforum.com/showthread...hreadid=525564

Template changes to existing workbooks
 

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

Template changes to existing workbooks
 

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


"farmkid21" wrote in message
...

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 how
do I make the changes to all of my existing workbooks without having to
reenter all the information in the new template?

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

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

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

Thank you.


--
farmkid21
------------------------------------------------------------------------
farmkid21's Profile: http://www.excelforum.com/member.php...o&userid=32170
View this thread: http://www.excelforum.com/showthread...hreadid=525564