Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sheet you want to copy contains the name...
When I copy a worksheet I get the repeated message box that the destination
workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and "zz". I have to select yes to each message box to allow the worksheet copy to complete itself. I do not have these names within the worksheet (chekced by InsertNameDefine). How can I delete these nsmaes that do not seem to exist? Thanks. |
#2
|
|||
|
|||
Names can be hidden, so Insert=Name=Define may not show them. In the
troublesome workbook make a blank sheet active, then go to the VBE (Alt+F11), do insert=Module and paste in the below code. Then do Alt+F11 to return to the worksheet and then do Tools=Macro=Macros, select ShowNames and click run. Sub ShowNames() i = 1 for each nm in activeWorkbook.Names cells(i,1).Value = nm.name cells(i,2).Value = nm.RefersTo cells(i,3).Value = nm.Visible i = i + 1 Next End Sub -- Regards, Tom Ogilvy "Michael31" wrote in message ... When I copy a worksheet I get the repeated message box that the destination workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and "zz". I have to select yes to each message box to allow the worksheet copy to complete itself. I do not have these names within the worksheet (chekced by InsertNameDefine). How can I delete these nsmaes that do not seem to exist? Thanks. |
#3
|
|||
|
|||
Thank-you very much Tom.
I added ActiveWorkbook.Names(nm.Name).Delete to your macro and deleted the buggers- I had over 100 unknown and unused names in the workbook. All were invisible as per your detection routine and the name's references made no sense. I must have created them sometime ago with some bad VB code. Thank you again and happy Easter. Michael Canada "Tom Ogilvy" wrote: Names can be hidden, so Insert=Name=Define may not show them. In the troublesome workbook make a blank sheet active, then go to the VBE (Alt+F11), do insert=Module and paste in the below code. Then do Alt+F11 to return to the worksheet and then do Tools=Macro=Macros, select ShowNames and click run. Sub ShowNames() i = 1 for each nm in activeWorkbook.Names cells(i,1).Value = nm.name cells(i,2).Value = nm.RefersTo cells(i,3).Value = nm.Visible i = i + 1 Next End Sub -- Regards, Tom Ogilvy "Michael31" wrote in message ... When I copy a worksheet I get the repeated message box that the destination workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and "zz". I have to select yes to each message box to allow the worksheet copy to complete itself. I do not have these names within the worksheet (chekced by InsertNameDefine). How can I delete these nsmaes that do not seem to exist? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet you want to copy contains the name...
Thanks. I had the same problem, but I got an error with the macro. I deleted
the line where i=2 and added Michael31's line. It now works. I don't know much about macros but I suspect that the error was because the orignal sources were no longer available. Mike "Michael31" wrote: Thank-you very much Tom. I added ActiveWorkbook.Names(nm.Name).Delete to your macro and deleted the buggers- I had over 100 unknown and unused names in the workbook. All were invisible as per your detection routine and the name's references made no sense. I must have created them sometime ago with some bad VB code. Thank you again and happy Easter. Michael Canada "Tom Ogilvy" wrote: Names can be hidden, so Insert=Name=Define may not show them. In the troublesome workbook make a blank sheet active, then go to the VBE (Alt+F11), do insert=Module and paste in the below code. Then do Alt+F11 to return to the worksheet and then do Tools=Macro=Macros, select ShowNames and click run. Sub ShowNames() i = 1 for each nm in activeWorkbook.Names cells(i,1).Value = nm.name cells(i,2).Value = nm.RefersTo cells(i,3).Value = nm.Visible i = i + 1 Next End Sub -- Regards, Tom Ogilvy "Michael31" wrote in message ... When I copy a worksheet I get the repeated message box that the destination workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and "zz". I have to select yes to each message box to allow the worksheet copy to complete itself. I do not have these names within the worksheet (chekced by InsertNameDefine). How can I delete these nsmaes that do not seem to exist? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to copy columns to sheet | Excel Discussion (Misc queries) | |||
Copy text from same cell on every sheet to title sheet? | Excel Discussion (Misc queries) | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Copy formula...sheet 2 sheet | New Users to Excel | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |