ExcelBanter - delete defined names

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - delete defined names (https://www.excelbanter.com/excel-programming/355222-delete-defined-names.html)

delete defined names

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

delete defined names

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

"Todd" wrote:

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

delete defined names

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

"Charlie" wrote:

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

"Todd" wrote:

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

delete defined names

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

"Todd" wrote:

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

"Charlie" wrote:

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

"Todd" wrote:

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

delete defined names

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

--
Regards,
Tom Ogilvy

"Charlie" wrote in message
...
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

"Todd" wrote:

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

"Charlie" wrote:

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

"Todd" wrote:

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