Duplicate Defined Names ?

[dpenny]

We are having a problem with semi duplicate defined names in one workbook. Scenario is as follows:
1) we have 10-20 pivot tables built off a data list using a dynamic defined name that adjusts with data quantity (standard idea)
2) these pivot tables are then maniplated by VBA code on their page fields
2a) results sometimes weird
3) we've copied some pivot tbls with CTRL-c & CTRL-v from workbook A to workbook B, etc. quite a few times since we have several persons working on the project. Also, we've copy/moved whole sheets quite a bit.
4) we find that the defined name data source ranges refer to the original workbook.

How can I have two defined names, say "db_data", on two sheets in one workbook that refer to different final ranges? (one in current WB, one in the copied from WB)

Isn't there some way to enforce only one defined name spelled the same throughout the WB? I thought defined names were GLOBAL to a WB.

The edit LINKS is grey. I've always taken this as infallible proof of no links?

What to do in general when copying pivot tables?

[tuph]

Excel 2003 warns me if I try to paste a range with a define name that's duplicated in the destination worksheet. In the warning box I click on Yes to use the name in the destination worksheet and it works okay.

[dpenny]

There is no copy/pasting going on. The defined names are used *only* to provide dynamic ranges to pivot tables.

When we insert/copy whole sheets from another WB the references to the ranges in the source WB come along.

This then causes duplicate defined names in the current WB. No messages, no nothing - it just happens.

Anyone any ideas on how to prevent this?