Range Names

[LAF]

I have 2 Excel files. I am removing some sheets from one file and then replacing them with sheets from the 2nd file. When I remove a sheet, it still leaves the references to the range names that existed on that sheet. The new sheet I'm copying in has range names that are the same as the old sheet. When I copy the sheet in from the 2nd file, it causes duplicate range names and problems. Is there a way to completely remove the range name references from the first file for the old sheets that are being removed?

[swatsp0p]

First, any reference to either a named Range OR to any one cell or range of cells will return #REF error when said sheet is deleted. Simply replacing the sheet with pre-named matching ranges will not correct this error.

However, prior to deleting the sheet, you can use Find/Replace to look in formulas for references to said Named Range and replace with a bogus entry (e.g. xxxxx). Repeat as needed for each Named Range. Then do a Find/Replace for the SheetName! (if any) that references cells in the sheet you will be deleting. Use a similar Replace With entry (e.g. yyyyy).

Then Delete your sheet, insert the new sheet, and repeat the Find/Replace, this time looking for 'xxxxx' or 'yyyyy' and Replace with correct Range name or Sheet name.

HTH

Bruce

[LAF]

That is part of the problem, but when I copy my new sheets into the file, I end up with 2 references to the same range name. Even though I deleted the old sheet, the range name references for that deleted sheet still remained in the file. Is there a way to not only delete the sheet but also delete all range name references that existed on that sheet?

[swatsp0p]

Let me understand this better. Are you saying that after you delete the sheet, in the Insert>Name>Define box (or in the RangeName dropdown box) the Range Names that were in the deleted sheet still exist? I cannot replicate that, as when I delete a sheet with a named range, the range disappears as well.

Verify that the range name still exists AFTER deletion and BEFORE inserting the new sheet.

Also, when you have duplicate range names, do they point to the same range (sheet name and cells)?

It is possible to have, for example, Sheet1!TestRange and Sheet2!TestRange as named ranges.

Is the inserted sheet the same sheet name as the deleted sheet?

As you can tell, I don't have a solution, but lots of things for you to look at.

Others can feel free to jump in with any suggestions.

Sorry I can't help more.

Bruce

[LAF]

If you go to sheet1 in your file and create range names test1 and test2, and then you delete the sheet, you will still see references to test1 and test2 in Insert-Name -Define.

[swatsp0p]

That is strange, as the range names disappear from the 'Name Box'. Other than manually going into the Define Range Name box and deleting, I am at a loss on this.

Sorry.

Bruce