I have a workbook with multiple sheets displaying similar info, these are also formatted and laid out identically. Is there a way to ammend parts of formulas in one sheet and get the other sheets to copy the changes?
Basically each sheet has a formula refering to 2009 information and we need to change the link to 2010. Short of manually deleting and typing the change in each cell, is there a way to do this?
Many thanks for your help.How can you change parts of formula in multiple Excel worksheets?Is the %26quot;link%26quot; for the 2009 info. a specific sheet in the existing workbook?
If yes, just make a copy of the 2009 sheet. Then change the name of the original 2009 sheet to whatever you want and add the 2010 data to it. When you change the name of the original sheet, all formulas that reference it will change as well.
Otherwise you could do it with a macro. Record a macro where you use the Find\Replace feature in the Edit menu to replace the old formula references with the new references. Say you called that macro %26quot;Macro1%26quot;. The macro below will loop Macro1 for all your sheets in the workbook
Sub Run_Macro1_For_All_Sheets()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Call Macro1
Next ws
End Sub
