Delete/Remove Duplicate Named Range based on scope

[jellybean1974]

Hi

I have a workbook that allows users to copy sheets into it from another workbook. The other workbook has the same named ranges in it, so when sheets are copied, my workbook ends up with duplicate named ranges (one of which has the other workbook's filename in it).

My workbook uses named ranges to reference images, so when the copied sheets are introduced, the wrong images are shown. I got around this by using VBA to delete from the "Refers To", all characters between [ and ]

This solved the wrong image issue, but still leaves me with duplicate named ranges, the only difference between them being scope (copied ones are Workbook scope and original ones are Sheet scope)

I have researched and found vba script that deletes all named ranges and vba script that deletes #REF! named ranges, but can't find example of vba to delete duplicate named range based on scope.

Is it even possible?

Thanks for any help

J

 

[Chihiro]

So which one are you trying to delete?

If deleting worksheet scope named range. You should be able search for "!" in name string and delete (or you can do reverse by searching for named range that doesn't contain "!" in name and delete).

This link may help.
http://stackoverflow.com/questions/1057189/how-to-change-named-range-scope

 

[jellybean1974]

Thanks Chihiro, that seems to have done the trick.