I have an Excel spreadsheet with about a hundred named ranges. I'd like to run a script to find certain strings within those named ranges and replace it with another string. The problem is about changing the names of named ranges while keeping the underlying cell references the same.
The standard Excel Search and Replace function doesn't work on named ranges.
For example: Named range = "Turnover_Shop_ABC_2018", and I want to replace the text "Shop_ABC" with "Store_XYZ". There are a few strings that I need to search and replace for but the macro doesn't need to be complicated: I don't mind running the script and manually updating the search strings each time.
Any help very much appreciated!