"A Formula or sheet..."

[BChat]

Windows Vista Ultimate - SP1 RC1
Office 2007 Professional

When I try to copy a worksheet I get the following:

"A formula or sheet you want to move or copy contains the name '_XXXX',
which already exists on the destination worksheet. Do you want to use this
version of the name?"

This messages appears many time before the sheet is finally copied. I have
been clicking the yes button and the task is completed. Is there a
work-around, or add in, that allows me to go in and delete all these names
so I can just copy the sheet w/o all the extra effort. It seems to me I ran
into this years ago and was able to correct the situation.

Any ideas appreciated.

BChat

 

[Pete_UK]

Before you move the sheet, click on the window of the destination workbook,
press ALT-F11 to open the VBE, press CTRL-G to enter the Intermediate window
and type this in:

For Each n In ActiveWorkbook.Names : n.Delete : Next n

and press <enter> at the end. This will remove all the named ranges in that
workbook. Close the VBE, click on the source file window and copy your sheet
across (I use CTRL-drag).

Hope this helps.

Pete

 

[BChat]

Thanks Pete -

I am going to copy and save this for future reference. Since my original
post, I remembered how I solved this issue in the past - the use of "Name
Manager", which I think was an add on then, but is now on the Formula tab of
the ribbon. I deleted the names on the sheet I was trying to copy (within
the same workbook) and all went well. I am not sure if the results are the
same as you'd get with your message, but sure looks similar - bye bye named
ranges.

Thanks again,
BChat


Before you move the sheet, click on the window of the destination workbook,
press ALT-F11 to open the VBE, press CTRL-G to enter the Intermediate window
and type this in:

For Each n In ActiveWorkbook.Names : n.Delete : Next n

and press <enter> at the end. This will remove all the named ranges in that
workbook. Close the VBE, click on the source file window and copy your sheet
across (I use CTRL-drag).

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks Pete -

I am going to copy and save this for future reference. Since my original
post, I remembered how I solved this issue in the past - the use of "Name
Manager", which I think was an add on then, but is now on the Formula tab of
the ribbon. I deleted the names on the sheet I was trying to copy (within
the same workbook) and all went well. I am not sure if the results are the
same as you'd get with your message, but sure looks similar - bye bye named
ranges.

Thanks again,
BChat


Before you move the sheet, click on the window of the destination workbook,
press ALT-F11 to open the VBE, press CTRL-G to enter the Intermediate window
and type this in:

For Each n In ActiveWorkbook.Names : n.Delete : Next n

and press <enter> at the end. This will remove all the named ranges in that
workbook. Close the VBE, click on the source file window and copy your sheet
across (I use CTRL-drag).

Hope this helps.

Pete











- Show quoted text -

 

[Shane Devenshire]

Hi,

This might work:

Since you are using Excel 2007 why not try the new Name Manager. Before you
copy, select the workbook whose names you do not want to use and choose
Formulas, Name Manager, select all the names you don't want and click
Delete. Then do your copying.

Cheers,
Shane