Deleting names in activesheet only

[David Bussell]

I been looking for a way to limit the macro to just remove names within a single sheet and have just tried out the solution from lionpants. But it doesn't seem to work for me. By using workbooks, all names are deleted. When I use activesheet the macro runs, but no names are deleted.
any ideas?

Sub ByeByeNamedRanges()
Dim nm As Name
On Error Resume Next
For Each nm In ActiveSheet.Names
nm.Delete
Next
On Error GoTo 0
End Sub
<!-- / message -->

 

[MickG]

Hi, Try this:-

[COLOR="Navy"]Sub[/COLOR] MG22Nov46
[COLOR="Navy"]Dim[/COLOR] myName [COLOR="Navy"]As[/COLOR] Name, Ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
      [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] myName [COLOR="Navy"]In[/COLOR] ActiveWorkbook.Names
      [COLOR="Navy"]If[/COLOR] myName.RefersToRange.Worksheet.Name = ActiveSheet.Name [COLOR="Navy"]Then[/COLOR]
         Msg = myName.Name & " = " & myName.RefersToRange.Address
         Ans = MsgBox("Do you want to delete:- " & Msg, vbYesNo + vbInformation)
         [COLOR="Navy"]If[/COLOR] Ans = vbYes [COLOR="Navy"]Then[/COLOR] myName.Delete
      [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] myName
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Weaver]

duplicated post

http://www.mrexcel.com/forum/showthread.php?t=431309

 

[David Bussell]

Thanks.
Apologies for the duplicate posting. I thought the first had not worked.
David