I have acquired a spreadsheet that has been used for many different
applications by many people. Consequently, several hundred names have been
applied to cells and/or references to other spreadsheets. Each time I creat a
copy of one of the worksheets, a dialogue box appears asking me if I want to
keep or rename the name that has been applied to cells in the sheet. I have
to either hit OK or rename all of them.

What I want to do is delete ALL of the names and start with a clean sheet
each time I copy it. It's very cumbersome deleting them one at a time in the
INSERT - NAME - DEFINE box.

How can I perform a MASS Name Delete?

Thanks.........TC

Hi

You can remove all names in the active workbook with this workbook.

Sub RemoveNames()
For Each n In ActiveWorkbook.Names
n.Delete
Next
End Sub

To inser the code:
Alt+F11 to open the VBA editor
Goto Insert Module
Copy the above code to the code sheet
Run the macro.

Hopes this helps.

---
Per

"TomCat" skrev i meddelelsen
...
I have acquired a spreadsheet that has been used for many different
applications by many people. Consequently, several hundred names have been
applied to cells and/or references to other spreadsheets. Each time I
creat a
copy of one of the worksheets, a dialogue box appears asking me if I want
to
keep or rename the name that has been applied to cells in the sheet. I
have
to either hit OK or rename all of them.

What I want to do is delete ALL of the names and start with a clean sheet
each time I copy it. It's very cumbersome deleting them one at a time in
the
INSERT - NAME - DEFINE box.

How can I perform a MASS Name Delete?

Thanks.........TC

Thanks for the reply, Per. I tried it and got an error on the n.Delete.

Sub RemoveNames()
For Each n In ActiveWorkbook.Names
n.Delete <------------error
Next
End Sub

"Per Jessen" wrote:

Hi

You can remove all names in the active workbook with this workbook.

Sub RemoveNames()
For Each n In ActiveWorkbook.Names
n.Delete
Next
End Sub

To inser the code:
Alt+F11 to open the VBA editor
Goto Insert Module
Copy the above code to the code sheet
Run the macro.

Hopes this helps.

---
Per

"TomCat" skrev i meddelelsen
...
I have acquired a spreadsheet that has been used for many different
applications by many people. Consequently, several hundred names have been
applied to cells and/or references to other spreadsheets. Each time I
creat a
copy of one of the worksheets, a dialogue box appears asking me if I want
to
keep or rename the name that has been applied to cells in the sheet. I
have
to either hit OK or rename all of them.

What I want to do is delete ALL of the names and start with a clean sheet
each time I copy it. It's very cumbersome deleting them one at a time in
the
INSERT - NAME - DEFINE box.

How can I perform a MASS Name Delete?

Thanks.........TC

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme

End Sub

--
__________________________________
HTH

Bob

"TomCat" wrote in message
...
I have acquired a spreadsheet that has been used for many different
applications by many people. Consequently, several hundred names have been
applied to cells and/or references to other spreadsheets. Each time I
creat a
copy of one of the worksheets, a dialogue box appears asking me if I want
to
keep or rename the name that has been applied to cells in the sheet. I
have
to either hit OK or rename all of them.

What I want to do is delete ALL of the names and start with a clean sheet
each time I copy it. It's very cumbersome deleting them one at a time in
the
INSERT - NAME - DEFINE box.

How can I perform a MASS Name Delete?

Thanks.........TC

Only problem is that there are a couple of hundred names in this sheet and
all are different names. I would have to list all of the names to do the
macro below. Is there some way to just delete all the names without listing
each one? Mass delete of n...? The sheet is formatted very intricately and
I'm trying to keep from recreating it several times in new workbooks (which
would solve the problem...).

"Bob Phillips" wrote:

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme

End Sub

--
__________________________________
HTH

Bob

"TomCat" wrote in message
...
I have acquired a spreadsheet that has been used for many different
applications by many people. Consequently, several hundred names have been
applied to cells and/or references to other spreadsheets. Each time I
creat a
copy of one of the worksheets, a dialogue box appears asking me if I want
to
keep or rename the name that has been applied to cells in the sheet. I
have
to either hit OK or rename all of them.

What I want to do is delete ALL of the names and start with a clean sheet
each time I copy it. It's very cumbersome deleting them one at a time in
the
INSERT - NAME - DEFINE box.

How can I perform a MASS Name Delete?

Thanks.........TC

No you don't, that is an exclusion list to avoid deleting system generated
names.

--
__________________________________
HTH

Bob

"TomCat" wrote in message
...
Only problem is that there are a couple of hundred names in this sheet and
all are different names. I would have to list all of the names to do the
macro below. Is there some way to just delete all the names without
listing
each one? Mass delete of n...? The sheet is formatted very intricately
and
I'm trying to keep from recreating it several times in new workbooks
(which
would solve the problem...).

"Bob Phillips" wrote:

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme

End Sub

--
__________________________________
HTH

Bob

"TomCat" wrote in message
...
I have acquired a spreadsheet that has been used for many different
applications by many people. Consequently, several hundred names have
been
applied to cells and/or references to other spreadsheets. Each time I
creat a
copy of one of the worksheets, a dialogue box appears asking me if I
want
to
keep or rename the name that has been applied to cells in the sheet. I
have
to either hit OK or rename all of them.

What I want to do is delete ALL of the names and start with a clean
sheet
each time I copy it. It's very cumbersome deleting them one at a time
in
the
INSERT - NAME - DEFINE box.

How can I perform a MASS Name Delete?

Thanks.........TC

Bob, I've a similar situation where I was pulling a lookup table from one spreadsheet to another. It was riddled with NAME references. I got tired of looking at it and found your post.

This worked like a charm!

Thanks very much for your post.



Bob Phillips wrote:

Deleting Multiple Names
19-Feb-09

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme

End Sub

--
__________________________________
HTH

Bob

"TomCat" wrote in message
...

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx