+ Reply to Thread
Results 1 to 6 of 6

delete defined names

  1. #1
    Todd
    Guest

    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. #2
    Charlie
    Guest

    RE: 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. #3
    Charlie
    Guest

    RE: 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


  4. #4
    Todd
    Guest

    RE: 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


  5. #5
    Charlie
    Guest

    RE: 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


  6. #6
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1