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
|