Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=IF problem | Excel Discussion (Misc queries) | |||
inserting sheets into existing workbooks w/o external references | Excel Worksheet Functions | |||
apply a template to existing worksheet | Excel Discussion (Misc queries) | |||
Can you link many workbooks to one template? And Update? | Excel Discussion (Misc queries) | |||
How can I get existing Excel workbooks to open in separate windows | Excel Worksheet Functions |