Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Delete Define names across workbook
I have a workbook with about 35 or so tabs. What happened was that on the
first sheet, I named a cell "pe" and then copied that worsheet about 30 times or so. Each worksheet has a different set of data. The problem is that each worksheet now has a cell named "pe". I only wanted the first sheet to have a cell named "pe" since I have data being linked to it (cell =pe, etc.) How can I remove the defined name on each worksheet without having to manually do it? Maybe there is a macro or something. Thanks Mike |
#2
|
|||
|
|||
On the main menu press Insert, then select Name, then select Define. This
will give you a list of all defined names. Go through them and delete the ones you don't want. This will be quicker than writing a macro of waiting for a response from here. Look on it as an education. It will remind you never to make the same mistake again. Pip pip "Mike" wrote: I have a workbook with about 35 or so tabs. What happened was that on the first sheet, I named a cell "pe" and then copied that worsheet about 30 times or so. Each worksheet has a different set of data. The problem is that each worksheet now has a cell named "pe". I only wanted the first sheet to have a cell named "pe" since I have data being linked to it (cell =pe, etc.) How can I remove the defined name on each worksheet without having to manually do it? Maybe there is a macro or something. Thanks Mike |
#3
|
|||
|
|||
create a new module in your workbook and paste in this code. be sure to
change the line that excludes Sheet1 so that it references the name of the sheet where you want to KEEP the name Option Explicit Sub DeletePE() Dim ws As Worksheet Dim rng As Range For Each ws In ThisWorkbook.Worksheets ' change Sheet1 in the next line to the name ' of the sheet where you WANT the range name If ws.Name < "Sheet1" Then On Error Resume Next Set rng = ws.Range("pe") If Err.Number = 0 Then ws.Names("pe").Delete End If End If Next End Sub "Mike" wrote: I have a workbook with about 35 or so tabs. What happened was that on the first sheet, I named a cell "pe" and then copied that worsheet about 30 times or so. Each worksheet has a different set of data. The problem is that each worksheet now has a cell named "pe". I only wanted the first sheet to have a cell named "pe" since I have data being linked to it (cell =pe, etc.) How can I remove the defined name on each worksheet without having to manually do it? Maybe there is a macro or something. Thanks Mike |
#4
|
|||
|
|||
If you work with names, do yourself a favor and get a copy of Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp Mike wrote: I have a workbook with about 35 or so tabs. What happened was that on the first sheet, I named a cell "pe" and then copied that worsheet about 30 times or so. Each worksheet has a different set of data. The problem is that each worksheet now has a cell named "pe". I only wanted the first sheet to have a cell named "pe" since I have data being linked to it (cell =pe, etc.) How can I remove the defined name on each worksheet without having to manually do it? Maybe there is a macro or something. Thanks Mike -- Dave Peterson |
#5
|
|||
|
|||
Duke,
Your solution worked perferctly. Thank you. Boris, when I go to insert and defined names, it only lists the pe on whatever sheet I am at, not all the pe(s) for all the worksheets. Thanks Everybody Mike "Dave Peterson" wrote in message ... If you work with names, do yourself a favor and get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp Mike wrote: I have a workbook with about 35 or so tabs. What happened was that on the first sheet, I named a cell "pe" and then copied that worsheet about 30 times or so. Each worksheet has a different set of data. The problem is that each worksheet now has a cell named "pe". I only wanted the first sheet to have a cell named "pe" since I have data being linked to it (cell =pe, etc.) How can I remove the defined name on each worksheet without having to manually do it? Maybe there is a macro or something. Thanks Mike -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I SUM TWO CELLS FROM ONE WORKBOOK TO ANOTHER WORKBOOK? | Excel Worksheet Functions | |||
copying cell names | Excel Discussion (Misc queries) | |||
alternate UI for Define Names ?? | Excel Discussion (Misc queries) | |||
How can I Copy cell names from one workbook to another? | Excel Discussion (Misc queries) | |||
Unprotect Workbook | Excel Discussion (Misc queries) |