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

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

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

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

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