CODEWITHSUNDEEP
vbaexcel
Ben
Ben

Reputation: 21705

Weird Excel bug when deleting sheet with named ranges?

I have an excel workbook that includes 2 worskheets, wkshtA and wkshtB. They both have local named ranges (the scope of each named range is just the parent sheet). Some cells in wksht B reference the named ranges in A, but none of the cells in A reference named ranges in B.

I have a macro that creates a copy of each sheet within the same workbook. This works fine.

The problem is when I delete worksheet A, my name manager has a bunch of #ref errors with all the names that were on worksheet A, but the scope of each has been changed to "Workbook." However, this only happens if I delete worksheet A before I delete worksheet B. If I delete them in the reverse order, all the named ranges in A get deleted with the sheet.

Another weird thing is if I delete A, then delete all the #ref errors, then delete B, then run the macro that makes new copies, then delete A again, the #ref errors don't reappear.

I'm stumped... Any ideas? Thanks.

Upvotes: 2

Views: 1164

Answers (1)

Ben
Ben

Reputation: 21705

Turns out the problem was the way I was defining my references from B to A. For instance, I had a range in A and a range in B both named "Premium". I had a snippet of code that said

worksheets("B").range("Premium").formula = "='A'!Premium"

This actually worked fine, but when I deleted worksheet A, the named range didn't delete. Changing the formula definition to

worksheets("B").range("Premium").formula = "='A'!" & worksheets("A").range("Premium").address

did the trick

Upvotes: 1

Related Questions