Excel copies named range from a different sheet when a sheet is copied

[Jacc]

This looks like an amazing Excel bug to me.
When I copy the sheet that says copy me it also creates a copy of a named range from the other sheet.
That wouldn't be so bad but I get a warning message as well.

Can you guys recreate this bug?

Or is it maybe a feature that I don't understand, that is quite possible.

This is actually a rather large application that is being used by many people in my company everyday, I just cut it down to bare minimum.

[Greg M]

Hi there,

Can you guys recreate this bug?

I've experienced this "phenomenon" in the past and, as far as I can tell, it occurs when the Named Ranges are defined at workbook level - it doesn't seem to occur when they are defined at worksheet level.

Hope this helps.

Regards,

Greg M

[Jacc]

Thanks Greg, I'll try that. I'm taking some time off now but I'll try remember posting some feedback once I get back to it.

Just for peace of mind, this is a bug right? It's not something I'm misunderstanding?

[AliGW]

I think it's intended behaviour, for the reason Greg has mentioned: if you have a named range whose level is set at workBOOK, then I think it entirely reasonable that Excel would assume you'd want that range on a clone of that sheet (because its scope is workbook). If the scope is workSHEET, then I think it reasonable for Excel to assume that you would not want it cloning if you copy the worksheet.

At least, that's my take on it. Not unexpected behaviour in my eyes.

[Jacc]

A little bit late but here is some extra noise to this story:
Sometimes it just creates a copy of the named range, sometimes it creates a copy AND throws a warning that the named range already exists.
Sometimes it seems to overwrite that new name definition every time you copy, so only one extra named range definition no matter how many times you copy.
Sometimes it creates a new definition and keeps the old one every time you copy. My application is based on copying sheets so I'm left with a ton of extra named ranges, each defined on a new sheet but still referring to the original named range which is on another sheet.

Why does Excel even have this feature of defining a variable on one sheet that is local to another sheet???

[AliGW]

Sometimes it creates a new definition and keeps the old one every time you copy.

This is a contradiction in terms!

I haven't experienced anything I'd consider illogical in this respect, but I use named ranges sparingly and carefully. I haven't looked at your particular workbook.

[Jacc]

See if you can recreate it. All you have to do is open my workbook, look at named ranges, drag-copy the worksheet by holding ctrl and then look at the named ranges again.

[Jacc]

I think it's intended behaviour, for the reason Greg has mentioned: if you have a named range whose level is set at workBOOK, then I think it entirely reasonable that Excel would assume you'd want that range on a clone of that sheet (because its scope is workbook). If the scope is workSHEET, then I think it reasonable for Excel to assume that you would not want it cloning if you copy the worksheet.

At least, that's my take on it. Not unexpected behaviour in my eyes.

That's kind of brave posting that without having opened and tried it on my workbook. The named range appears local on the new sheet but refers to the original sheet. Is that really intentional?

[AliGW]

I've tried several times, both with content enabled and content not enabled, and I cannot reproduce this. However, as you have direct access to the source of the query that feeds the table, I think that might have something to do with it. When it happens for you, I presume it's also duplicating the query - is it?

That's kind of brave posting that without having opened and tried it on my workbook.

How so? I have worked with many workbooks that contain named ranges in my time - I think that qualifies me to comment. You didn't make a similar comment to Greg, and his comments were based on his own experience, as were mine. In any case, I said "I think ..."

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - hope you found my comments useful.

Best regards,

Greg M

[Jacc]

I tried to inserts pictures but they only show up as attachments? How do I make them pictures in the post and not attachments?

Anyway, I deleted the queries, forgot to do that before. It made no difference.

This is what I see on my computer. Is this intended behavior AliGW ?
Attachment 831873
Attachment 831875

[Jacc]

How do I post a picture of this in a post?

[AliGW]

Those attachments aren’t valid so I can’t see them. Attach the images as you would attach a workbook.