Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete defined names | Excel Discussion (Misc queries) | |||
How to delete all defined names from a workbook? | Excel Worksheet Functions | |||
How to delete all defined names from a workbook? | Links and Linking in Excel | |||
Defined Names | Excel Programming | |||
Macro to delete Defined Names | Excel Programming |