Excel – Copy Sheets Problem (Excel 2000 SP2)

Topic

New Reply

I have an Excel workbook with a significant number of sheets. I have one sheet that has defined names, I am trying to create a copy of this sheet. When I do so I am prompted with the following

” A formula or sheet you want to move or copy contains the name ‘wrn.print’, which already exists on the detination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet select Yes To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Names Conflict dialog box”

If I select Yes it continues if I select NO it prompts me to enter a different name.

I am unable to locate anywhere in the sheet where the formula or name ‘wrn.print’ is lcoated, I presume this is a defined name. Some questions

Is it a defined name?
If it is how can I locate the defined name?
What is the message trying to tell me?
How can I amend the workbook so that the worksheet copy does not prompt me with the above?

All help much appreciated

Regards

John

Reply | Quote

Replies

With the sheet that you’re trying to copy active, from the menu bar select Insert | Name | Define (or press Ctrl + F3). Do you see wrn.print listed? From your description, I would think you must. It is also probably a worksheet level name, maybe defined in all your sheets(?)

Reply | Quote

Hi Colin,

wrn.print is not listed. How can I establish if it is defined in all sheets?

Regards

John

Reply | Quote

Oh. Then I’m not sure what to suggest. WorkBOOK level names are valid in all sheets, but from your description I was guessing that each sheet had wrn.print as a workSHEET level name. Not so, apparently.
Maybe somebody else has a suggestion? Would anybody know if it could be related to Excel4 macros?

Reply | Quote

I would guess that this is a defined print range still there from some previous user/operation. You might try the Edit Menu/Find/”wrn.print” and see if you can find where in the sheet the reference exists. You also might look at Views/Custom Views and see if the “wrn.print” is listed as a custom view.

Reply | Quote

Hi Mike,

I have tried this too, again no joy, I have also tried F3 and the name does not appear in the paste list. I’m at my wits end to figure out where this wrn.print is located.

Regards

John

Reply | Quote

In the formula bar, is it in the drop down list? If not, Steve’s solution might be the best.

Reply | Quote

Here it is!

Open a new workbook. Go to View, Report Manager. Add a Report. Copy the Sheet. In my case, I used the name test and I received the message A formula or sheet contains the name wrn.Test.

The solution, use the Delete command button in the report manager.

Reply | Quote

Is that an add-in you have installed? I’m not familiar with Report Manager so I assume it’s not a built-in Excel feature.

Reply | Quote

It is one of the standard addins that ship with excel.

Reply | Quote

I knew as soon as I wrote “I assume” that I’d be proved wrong!

Reply | Quote

Hi Rory,

the utlity can be found at

http://www.cpearson.com/excel/xltools.htm%5B/url%5D

Regards

John

Reply | Quote

[indent]

---

[Report Manager] is one of the standard addins that ship with excel

---

[/indent]

Well, it used to anyway. Version 2002 has eliminated it, although the user can still download it extra from the MS site.

-Lenny

Reply | Quote

Hi Cecil,

Your a star, it works a treat, thank you for all your help.

Regards

John

Reply | Quote

Go over to Bill Manville’s page http://www.bmsltd.ie/MVP/Default.htm and download the addin “FindLink.xla”.

Edited Mar 13th 2004 to update link

Reply | Quote

Hi Cecil,

I have done this, still no joy, I think this is because it is not a link rather a defined name of some sort.

Regards

John

Reply | Quote

Have you hit and selected the “Paste List” command button on the lower right. If so, does the name appear in the list?

I played with v4 macros a bit, and I did not see where there names behaved any differently than any other range name.

Reply | Quote

Range name could be hidden.
Go the Immediate screen of VB (alt-F11, ctrl-G) and enter:
names(“wrn.print”).visible = True
to “unhide” it

Enter:
? range(“wrn.print”).Address
to get its address (you have access to alot of other methods and properties)

Entering in:
names(“wrn.print).delete
Should delete the name (if that is what you want to do)

Steve

Reply | Quote

Hi Steve,

No joy, sorry. What I have been able to do using a utility i downloaded is extract all the defined names in the workbook below is an example of what is returned

wrn.Print. ={#N/A,#N/A,FALSE,”Assumps”;#N/A,#N/A,FALSE,”Data”;#N/A,#N/A,FALSE,”Data (2)”;#N/A,#N/A,FALSE,”AcqyrP&L”;#N/A,#N/A,FALSE,”Acq+1P&L”;#N/A,#N/A,FALSE,”Acq+2P&L”;#N/A,#N/A,FALSE,”Cashflow”;#N/A,#N/A,FALSE,”BalSheet”;#N/A,#N/A,FALSE,”Bidder”;#N/A,#N/A,FALSE,”Tar

Reply | Quote

Strangely strange. Presumably every fourth parameter (in quotes) is a sheet name from the workbook? Not sure if knowing that gets us anywhere, though. If it’s not related to an earlier version of Excel, maybe the name(s) were created by an add-in? Do you have any add-ins loaded (apart from the Bill Manville utility)? Again, though, I don’t know if that’s going to help in figuring out how to delete them! Would you be able to post the workbook (maybe with a reduced number of sheets to cut down on the size)?

Reply | Quote