Copy sheet in Excel - Name conflict error

Hi

Wonder if anyone of you encounter this problem before? When I try to copy
worksheet in a workbook, the following prompt comes up, indicating name
conflict.

"A formula or sheet you want to move or copy contains the name 'aaaa', which
already exists on the destination worksheet. Do you want to use this version
of the name?
- To use the name as defined in the destination sheet, click Yes
- To rename the range referred to in the formula or worksheet, click No, and
enter a new name in the Name Conflict dialog box"

When I clicked yes, another prompt comes up indicating other name conflicts,
so I had to click yes many times before I can succeed in copying the sheet.
This is extremely time-consuming, and I worry whether this could result in
errors.

Apparently, this problem arises because the workbook contain worksheets
which I have copied from other workbooks. Those other workbooks have
programs/macros/name inserted which I inherit over when I copy sheets over.

To solve the problem, I have tried deleting all names in my workbook, but
the problem still persist. It seems that the only solution I have is to re-do
my entire workbook (gasp!).

Really hope there are some kind souls out there who know a quick solution to
this problem.

Thanks.

Try "localising" your names to worksheets that are going to be copied.
So, if you have a worksheet called "Sheet1"
and a name MyName that refers to range Sheet1!$A$1
then delete MyName and then redefine it as
Sheet1!MyName
refers to
Sheet1!$A$1

Note the "Sheet1!" prefix to the definition of the Name.

You may still run into problems if you use data validation that refers to
drop-down lists where the name of the list is localised to a different
worksheet, or conditional formatting where the "Fomula Is" refers to a named
range that is localised to another sheet.

Probably good practice to avoid duplicating global (not tied to sheet) names
and local names. It can cause confusion over which name is being called.

Jan Karel Pieterse's "Name Manager" is a useful add-in tool for clearing out
names that contain errors, identifying duplicate global/local names,
localising names that you have already defined as global and do not want to
go to the trouble of deleting first, and a whole host of other goodies.

--
Return email address is not as DEEP as it appears
"Cindy" wrote in message
...
Hi

Wonder if anyone of you encounter this problem before? When I try to copy
worksheet in a workbook, the following prompt comes up, indicating name
conflict.

"A formula or sheet you want to move or copy contains the name 'aaaa',
which
already exists on the destination worksheet. Do you want to use this
version
of the name?
- To use the name as defined in the destination sheet, click Yes
- To rename the range referred to in the formula or worksheet, click No,
and
enter a new name in the Name Conflict dialog box"

When I clicked yes, another prompt comes up indicating other name
conflicts,
so I had to click yes many times before I can succeed in copying the
sheet.
This is extremely time-consuming, and I worry whether this could result in
errors.

Apparently, this problem arises because the workbook contain worksheets
which I have copied from other workbooks. Those other workbooks have
programs/macros/name inserted which I inherit over when I copy sheets
over.

To solve the problem, I have tried deleting all names in my workbook, but
the problem still persist. It seems that the only solution I have is to
re-do
my entire workbook (gasp!).

Really hope there are some kind souls out there who know a quick solution
to
this problem.

Thanks.

On other common thing to worry about: If you later delete a worksheet that
contains localised named ranges, it may not automatically delete the named
ranges but rather globalise them and substitute #REF! errors for deleted
ranges. Usually not critical but clutters it up. Better to delete such
sheets by a VBA macro that first deletes any names that are local to the (to
be) deleted sheet, I find.

"Jack Schitt" wrote in message
...
Try "localising" your names to worksheets that are going to be copied.
So, if you have a worksheet called "Sheet1"
and a name MyName that refers to range Sheet1!$A$1
then delete MyName and then redefine it as
Sheet1!MyName
refers to
Sheet1!$A$1

Note the "Sheet1!" prefix to the definition of the Name.

You may still run into problems if you use data validation that refers to
drop-down lists where the name of the list is localised to a different
worksheet, or conditional formatting where the "Fomula Is" refers to a
named
range that is localised to another sheet.

Probably good practice to avoid duplicating global (not tied to sheet)
names
and local names. It can cause confusion over which name is being called.

Jan Karel Pieterse's "Name Manager" is a useful add-in tool for clearing
out
names that contain errors, identifying duplicate global/local names,
localising names that you have already defined as global and do not want
to
go to the trouble of deleting first, and a whole host of other goodies.

--
Return email address is not as DEEP as it appears
"Cindy" wrote in message
...
Hi

Wonder if anyone of you encounter this problem before? When I try to
copy
worksheet in a workbook, the following prompt comes up, indicating name
conflict.

"A formula or sheet you want to move or copy contains the name 'aaaa',
which
already exists on the destination worksheet. Do you want to use this
version
of the name?
- To use the name as defined in the destination sheet, click Yes
- To rename the range referred to in the formula or worksheet, click No,
and
enter a new name in the Name Conflict dialog box"

When I clicked yes, another prompt comes up indicating other name
conflicts,
so I had to click yes many times before I can succeed in copying the
sheet.
This is extremely time-consuming, and I worry whether this could result
in
errors.

Apparently, this problem arises because the workbook contain worksheets
which I have copied from other workbooks. Those other workbooks have
programs/macros/name inserted which I inherit over when I copy sheets
over.

To solve the problem, I have tried deleting all names in my workbook,
but
the problem still persist. It seems that the only solution I have is to
re-do
my entire workbook (gasp!).

Really hope there are some kind souls out there who know a quick
solution
to
this problem.

Thanks.