Global Replace

I've inherited a workbook for creating monthly projections for 3 bienniums. The workbook has 58 tabs, all covered with formulas, tables, pivots, etc. Currently there are 3 of the same workbook, each covering a two-year period (FY12/13, FY14/15, FY16/17). I want to tame this beast, starting by normalising the naming conventions. For one example, in the 14/15 version of the workbook I have a tab named "Relative 2015 CPA Paid", which has a column named "FY15 Rel CPA Paid". So in this instance I'd want to replace "Relative 2015" with "FY15 Rel" for the entire workbook (all tab names, column headings, all formulas, etc). There are multiple replacements I'll need to do, so I'd like to be able to do it globally, maybe turning off Calculations while the formulas are being updated.

At the same time, I'd like to replace all instances of "FY15" with "FY" and the value in my ENTER sheet, cell D3, and "FY14" with "FY" and the value in my ENTER sheet, cell D2,so I could use just one workbook, and I'd only need to update the values in my ENTER sheet to update all the names in my workbook.

I'd appreciate some help in coming up with a solution.

Thanks in advance for any help you can give,
John

Okay, I think I've solved the first part of my problem. I married together two "replace" macros I already had in my toolbox, so now I can replace globally on demand. Now I need help on the second part, updating all FY values in my workbook just by changing the values in my Enter sheet.

Please Login or Register  to view this content.