delete defined names

[Guest]

I am trying to delete the defined names out of a series of worksheets in a
workbook. I recorded this macro and it doesn't work. It stops at the first
task. What should I add in to make it work?

Thanks,


Todd


Sub macro1()
'
' macro1 Macro
' Macro recorded 3/6/2006 by tlandwert
'
' Keyboard Shortcut: Ctrl+e
'
ActiveWorkbook.Names("BaselineContingency").Delete
ActiveWorkbook.Names("BaselineLabor").Delete
ActiveWorkbook.Names("BaselineMatl").Delete
ActiveWorkbook.Names("BaselineMillTime").Delete
ActiveWorkbook.Names("BaselineOvenTime").Delete
ActiveWorkbook.Names("BaselineSupv").Delete
ActiveWorkbook.Names("BaselineOther").Delete
ActiveWorkbook.Names("BaselineTotal").Delete
End Sub

 

[Guest]

Since you recorded the macro by (I presume) deleting the named ranges
manually the macro will fail because the names are already deleted. One way
to delete all of the named ranges in a workbook is:

Private Sub DeleteAllNames()

Dim NamedRange As Name

For Each NamedRange In ActiveWorkbook.Names
NamedRange.Delete
Next NamedRange

End Sub

 

[Guest]

Thanks Charlie,

I think you are moving me in the right direction. The only thing is that
each worksheet has as many as 30 defined names and I only want to delete
these 8.


Todd

 

[Guest]

Ok, well, you could put in an On Error Resume Next statement to skip the
statements of the already deleted names.

On Error Resume Next
ActiveWorkbook.Names("BaselineContingency").Delete
ActiveWorkbook.Names("BaselineLabor").Delete
ActiveWorkbook.Names("BaselineMatl").Delete
ActiveWorkbook.Names("BaselineMillTime").Delete
ActiveWorkbook.Names("BaselineOvenTime").Delete
ActiveWorkbook.Names("BaselineSupv").Delete
ActiveWorkbook.Names("BaselineOther").Delete
ActiveWorkbook.Names("BaselineTotal").Delete

 

[Tom Ogilvy]

If you could have sheet level names as well:

Sub Delete8Names()
Dim sh as Worksheet
On Error Resume Next
for each sh in Activeworkbook.worksheets
sh.Names("BaselineContingency").Delete
sh.Names("BaselineLabor").Delete
sh.Names("BaselineMatl").Delete
sh.Names("BaselineMillTime").Delete
sh.Names("BaselineOvenTime").Delete
sh.Names("BaselineSupv").Delete
sh.Names("BaselineOther").Delete
sh.Names("BaselineTotal").Delete
Next
ActiveWorkbook.Names("BaselineContingency").Delete
ActiveWorkbook.Names("BaselineLabor").Delete
ActiveWorkbook.Names("BaselineMatl").Delete
ActiveWorkbook.Names("BaselineMillTime").Delete
ActiveWorkbook.Names("BaselineOvenTime").Delete
ActiveWorkbook.Names("BaselineSupv").Delete
ActiveWorkbook.Names("BaselineOther").Delete
ActiveWorkbook.Names("BaselineTotal").Delete
End Sub