+ Reply to Thread
Results 1 to 14 of 14

Deleting multiple Chart Tabs/sheets

  1. #1
    CLR
    Guest

    Deleting multiple Chart Tabs/sheets

    Hi All......

    I am working on a program that creates 37 different Charts. Each gets
    created as it's own sheet/tab. I never know how many will be created/deleted
    during the course of a session. When the session is complete, I would like a
    macro to delete all Chart Tab/sheets, regardless of their "Chart 22" or
    "Chart 12" numbers which Excel assigns them....I would like it to delete all
    existing Chart/tab between numbers 1 and 50 inclusive, but NOT non-chart
    tabs/sheets. Recording the macro, just don't seem to get me there.......any
    help would be much appreciated.

    TIA
    Vaya con Dios,
    Chuck, CABGx3



  2. #2
    Nick Hodge
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    Chuck

    Your question is a little ambiguous as you say it creates 37charts but you
    want to delete 1 to 50. If it is ALL chart sheets you want to delete the
    code below will do it

    Sub DeleteChartSheets()
    Dim sht As Object
    Application.DisplayAlerts = False
    For Each sht In ThisWorkbook.Sheets
    If sht.Type = 3 Then sht.Delete
    Next sht
    Application.DisplayAlerts = True
    End Sub

    --
    HTH
    Nick Hodge
    Microsoft MVP - Excel
    Southampton, England
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    "CLR" <CLR@discussions.microsoft.com> wrote in message
    news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    > Hi All......
    >
    > I am working on a program that creates 37 different Charts. Each gets
    > created as it's own sheet/tab. I never know how many will be
    > created/deleted
    > during the course of a session. When the session is complete, I would
    > like a
    > macro to delete all Chart Tab/sheets, regardless of their "Chart 22" or
    > "Chart 12" numbers which Excel assigns them....I would like it to delete
    > all
    > existing Chart/tab between numbers 1 and 50 inclusive, but NOT non-chart
    > tabs/sheets. Recording the macro, just don't seem to get me
    > there.......any
    > help would be much appreciated.
    >
    > TIA
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >




  3. #3
    Shawn O'Donnell
    Guest

    RE: Deleting multiple Chart Tabs/sheets

    "CLR" wrote:
    > I would like a macro to delete all Chart Tab/sheets, regardless of
    > their "Chart 22" or "Chart 12" numbers which Excel assigns them


    Stand-alone chart sheets are kept in a collection called "Charts" that
    belongs to Workbook objects like ActiveWorkbook. You can step through the
    collection and delete each sheet, if that's what you really want to do...

    Here's a no-going-back macro. If you want to at least think about each
    sheet for a second, you can comment out the DisableAlert lines.

    Sub deleteAllChartSheets()
    Dim doomedChart As Variant
    Application.DisplayAlerts = False
    For Each doomedChart In ActiveWorkbook.Charts
    doomedChart.Delete
    Next doomedChart
    Application.DisplayAlerts = True
    End Sub


  4. #4
    CLR
    Guest

    RE: Deleting multiple Chart Tabs/sheets

    Your code does a FINE job Shawn, exactly what I wanted.......(and in a hurry
    too),,,<g>

    Many many thanks.......
    Vaya con Dios,
    Chuck, CABGx3



    "Shawn O'Donnell" wrote:

    > "CLR" wrote:
    > > I would like a macro to delete all Chart Tab/sheets, regardless of
    > > their "Chart 22" or "Chart 12" numbers which Excel assigns them

    >
    > Stand-alone chart sheets are kept in a collection called "Charts" that
    > belongs to Workbook objects like ActiveWorkbook. You can step through the
    > collection and delete each sheet, if that's what you really want to do...
    >
    > Here's a no-going-back macro. If you want to at least think about each
    > sheet for a second, you can comment out the DisableAlert lines.
    >
    > Sub deleteAllChartSheets()
    > Dim doomedChart As Variant
    > Application.DisplayAlerts = False
    > For Each doomedChart In ActiveWorkbook.Charts
    > doomedChart.Delete
    > Next doomedChart
    > Application.DisplayAlerts = True
    > End Sub
    >


  5. #5
    CLR
    Guest

    RE: Deleting multiple Chart Tabs/sheets

    I tried to thank you earlier Shawn, but the system refused my reply.......

    Your code works FINE, exactly what I wanted..........(and in a hurry
    too).....<g>

    Many many thanks,
    Vaya con Dios,
    Chuck, CABGx3




    "Shawn O'Donnell" wrote:

    > "CLR" wrote:
    > > I would like a macro to delete all Chart Tab/sheets, regardless of
    > > their "Chart 22" or "Chart 12" numbers which Excel assigns them

    >
    > Stand-alone chart sheets are kept in a collection called "Charts" that
    > belongs to Workbook objects like ActiveWorkbook. You can step through the
    > collection and delete each sheet, if that's what you really want to do...
    >
    > Here's a no-going-back macro. If you want to at least think about each
    > sheet for a second, you can comment out the DisableAlert lines.
    >
    > Sub deleteAllChartSheets()
    > Dim doomedChart As Variant
    > Application.DisplayAlerts = False
    > For Each doomedChart In ActiveWorkbook.Charts
    > doomedChart.Delete
    > Next doomedChart
    > Application.DisplayAlerts = True
    > End Sub
    >


  6. #6
    Peter T
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    To be extremely pedantic, sorry <g>, sht.Type = 3 could also be Macro sheet.

    Regards,
    Peter T

    "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
    news:u10Gh3CIFHA.1280@TK2MSFTNGP09.phx.gbl...
    > Chuck
    >
    > Your question is a little ambiguous as you say it creates 37charts but you
    > want to delete 1 to 50. If it is ALL chart sheets you want to delete the
    > code below will do it
    >
    > Sub DeleteChartSheets()
    > Dim sht As Object
    > Application.DisplayAlerts = False
    > For Each sht In ThisWorkbook.Sheets
    > If sht.Type = 3 Then sht.Delete
    > Next sht
    > Application.DisplayAlerts = True
    > End Sub
    >
    > --
    > HTH
    > Nick Hodge
    > Microsoft MVP - Excel
    > Southampton, England
    > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >
    >
    > "CLR" <CLR@discussions.microsoft.com> wrote in message
    > news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    > > Hi All......
    > >
    > > I am working on a program that creates 37 different Charts. Each gets
    > > created as it's own sheet/tab. I never know how many will be
    > > created/deleted
    > > during the course of a session. When the session is complete, I would
    > > like a
    > > macro to delete all Chart Tab/sheets, regardless of their "Chart 22" or
    > > "Chart 12" numbers which Excel assigns them....I would like it to delete
    > > all
    > > existing Chart/tab between numbers 1 and 50 inclusive, but NOT non-chart
    > > tabs/sheets. Recording the macro, just don't seem to get me
    > > there.......any
    > > help would be much appreciated.
    > >
    > > TIA
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >

    >
    >




  7. #7
    Nick Hodge
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    Indeed...making the presumption that it was post XL95 or that someone had
    not added one in a later version. It at least leaves the worksheets which
    was the OP's request.

    Good pick-up though

    --
    HTH
    Nick Hodge
    Microsoft MVP - Excel
    Southampton, England
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    "Peter T" <peter_t@discussions> wrote in message
    news:u8p4AFDIFHA.3336@TK2MSFTNGP10.phx.gbl...
    > To be extremely pedantic, sorry <g>, sht.Type = 3 could also be Macro
    > sheet.
    >
    > Regards,
    > Peter T
    >
    > "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
    > news:u10Gh3CIFHA.1280@TK2MSFTNGP09.phx.gbl...
    >> Chuck
    >>
    >> Your question is a little ambiguous as you say it creates 37charts but
    >> you
    >> want to delete 1 to 50. If it is ALL chart sheets you want to delete the
    >> code below will do it
    >>
    >> Sub DeleteChartSheets()
    >> Dim sht As Object
    >> Application.DisplayAlerts = False
    >> For Each sht In ThisWorkbook.Sheets
    >> If sht.Type = 3 Then sht.Delete
    >> Next sht
    >> Application.DisplayAlerts = True
    >> End Sub
    >>
    >> --
    >> HTH
    >> Nick Hodge
    >> Microsoft MVP - Excel
    >> Southampton, England
    >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >>
    >>
    >> "CLR" <CLR@discussions.microsoft.com> wrote in message
    >> news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    >> > Hi All......
    >> >
    >> > I am working on a program that creates 37 different Charts. Each gets
    >> > created as it's own sheet/tab. I never know how many will be
    >> > created/deleted
    >> > during the course of a session. When the session is complete, I would
    >> > like a
    >> > macro to delete all Chart Tab/sheets, regardless of their "Chart 22" or
    >> > "Chart 12" numbers which Excel assigns them....I would like it to
    >> > delete
    >> > all
    >> > existing Chart/tab between numbers 1 and 50 inclusive, but NOT
    >> > non-chart
    >> > tabs/sheets. Recording the macro, just don't seem to get me
    >> > there.......any
    >> > help would be much appreciated.
    >> >
    >> > TIA
    >> > Vaya con Dios,
    >> > Chuck, CABGx3
    >> >
    >> >

    >>
    >>

    >
    >




  8. #8
    CLR
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    Sorry about that Nick............I didn't see your post on my newsgroup
    until AFTER I had already responded to Shawn's, and then came home from
    work.......... yet here at home it shows as coming in BEFORE his.......if
    any event, your's works great as well............I asked for 1-50 because I
    thought I wanted to adjust the range from time to time.........both of you
    guy's macros delete ALL of the charts, which is fine-ok in this
    case..........if I make 20 charts and delete them manually, the next chart
    comes up #21, and if I go ahead then and make another 30 then I'm up to
    Chart #50 but only have 30.........that's why I asked the question the way I
    did........thought I could take it out well beyond the range I might
    use.......but deleting them all works too.......It's interesting to see two
    different versions of code to do the same thing..........BTW, "where" does
    one find out that "sht. Type=3" is applicable in this case?

    Thanks again,
    Vaya con Dios,
    Chuck, CABGx3




    "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
    news:u10Gh3CIFHA.1280@TK2MSFTNGP09.phx.gbl...
    > Chuck
    >
    > Your question is a little ambiguous as you say it creates 37charts but you
    > want to delete 1 to 50. If it is ALL chart sheets you want to delete the
    > code below will do it
    >
    > Sub DeleteChartSheets()
    > Dim sht As Object
    > Application.DisplayAlerts = False
    > For Each sht In ThisWorkbook.Sheets
    > If sht.Type = 3 Then sht.Delete
    > Next sht
    > Application.DisplayAlerts = True
    > End Sub
    >
    > --
    > HTH
    > Nick Hodge
    > Microsoft MVP - Excel
    > Southampton, England
    > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >
    >
    > "CLR" <CLR@discussions.microsoft.com> wrote in message
    > news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    > > Hi All......
    > >
    > > I am working on a program that creates 37 different Charts. Each gets
    > > created as it's own sheet/tab. I never know how many will be
    > > created/deleted
    > > during the course of a session. When the session is complete, I would
    > > like a
    > > macro to delete all Chart Tab/sheets, regardless of their "Chart 22" or
    > > "Chart 12" numbers which Excel assigns them....I would like it to delete
    > > all
    > > existing Chart/tab between numbers 1 and 50 inclusive, but NOT non-chart
    > > tabs/sheets. Recording the macro, just don't seem to get me
    > > there.......any
    > > help would be much appreciated.
    > >
    > > TIA
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >

    >
    >




  9. #9
    Nick Hodge
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    Just run some code to find the type and send it to the debug window

    For each sh in thisworkbook.sheets
    Debug.Print sh.type
    Next sh

    --
    HTH
    Nick Hodge
    Microsoft MVP - Excel
    Southampton, England
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    "CLR" <croberts@tampabay.rr.com> wrote in message
    news:eZZ0WAEIFHA.2924@TK2MSFTNGP15.phx.gbl...
    > Sorry about that Nick............I didn't see your post on my newsgroup
    > until AFTER I had already responded to Shawn's, and then came home from
    > work.......... yet here at home it shows as coming in BEFORE his.......if
    > any event, your's works great as well............I asked for 1-50 because
    > I
    > thought I wanted to adjust the range from time to time.........both of you
    > guy's macros delete ALL of the charts, which is fine-ok in this
    > case..........if I make 20 charts and delete them manually, the next chart
    > comes up #21, and if I go ahead then and make another 30 then I'm up to
    > Chart #50 but only have 30.........that's why I asked the question the way
    > I
    > did........thought I could take it out well beyond the range I might
    > use.......but deleting them all works too.......It's interesting to see
    > two
    > different versions of code to do the same thing..........BTW, "where" does
    > one find out that "sht. Type=3" is applicable in this case?
    >
    > Thanks again,
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    >
    > "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
    > news:u10Gh3CIFHA.1280@TK2MSFTNGP09.phx.gbl...
    >> Chuck
    >>
    >> Your question is a little ambiguous as you say it creates 37charts but
    >> you
    >> want to delete 1 to 50. If it is ALL chart sheets you want to delete the
    >> code below will do it
    >>
    >> Sub DeleteChartSheets()
    >> Dim sht As Object
    >> Application.DisplayAlerts = False
    >> For Each sht In ThisWorkbook.Sheets
    >> If sht.Type = 3 Then sht.Delete
    >> Next sht
    >> Application.DisplayAlerts = True
    >> End Sub
    >>
    >> --
    >> HTH
    >> Nick Hodge
    >> Microsoft MVP - Excel
    >> Southampton, England
    >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >>
    >>
    >> "CLR" <CLR@discussions.microsoft.com> wrote in message
    >> news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    >> > Hi All......
    >> >
    >> > I am working on a program that creates 37 different Charts. Each gets
    >> > created as it's own sheet/tab. I never know how many will be
    >> > created/deleted
    >> > during the course of a session. When the session is complete, I would
    >> > like a
    >> > macro to delete all Chart Tab/sheets, regardless of their "Chart 22" or
    >> > "Chart 12" numbers which Excel assigns them....I would like it to
    >> > delete
    >> > all
    >> > existing Chart/tab between numbers 1 and 50 inclusive, but NOT
    >> > non-chart
    >> > tabs/sheets. Recording the macro, just don't seem to get me
    >> > there.......any
    >> > help would be much appreciated.
    >> >
    >> > TIA
    >> > Vaya con Dios,
    >> > Chuck, CABGx3
    >> >
    >> >

    >>
    >>

    >
    >




  10. #10
    CLR
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    Thanks anyway Nick, but that is totally "Greek" to me...

    Vaya con Dios,
    Chuck, CABGx3



    "Nick Hodge" wrote:

    > Just run some code to find the type and send it to the debug window
    >
    > For each sh in thisworkbook.sheets
    > Debug.Print sh.type
    > Next sh
    >
    > --
    > HTH
    > Nick Hodge
    > Microsoft MVP - Excel
    > Southampton, England
    > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >
    >
    > "CLR" <croberts@tampabay.rr.com> wrote in message
    > news:eZZ0WAEIFHA.2924@TK2MSFTNGP15.phx.gbl...
    > > Sorry about that Nick............I didn't see your post on my newsgroup
    > > until AFTER I had already responded to Shawn's, and then came home from
    > > work.......... yet here at home it shows as coming in BEFORE his.......if
    > > any event, your's works great as well............I asked for 1-50 because
    > > I
    > > thought I wanted to adjust the range from time to time.........both of you
    > > guy's macros delete ALL of the charts, which is fine-ok in this
    > > case..........if I make 20 charts and delete them manually, the next chart
    > > comes up #21, and if I go ahead then and make another 30 then I'm up to
    > > Chart #50 but only have 30.........that's why I asked the question the way
    > > I
    > > did........thought I could take it out well beyond the range I might
    > > use.......but deleting them all works too.......It's interesting to see
    > > two
    > > different versions of code to do the same thing..........BTW, "where" does
    > > one find out that "sht. Type=3" is applicable in this case?
    > >
    > > Thanks again,
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > >
    > > "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
    > > news:u10Gh3CIFHA.1280@TK2MSFTNGP09.phx.gbl...
    > >> Chuck
    > >>
    > >> Your question is a little ambiguous as you say it creates 37charts but
    > >> you
    > >> want to delete 1 to 50. If it is ALL chart sheets you want to delete the
    > >> code below will do it
    > >>
    > >> Sub DeleteChartSheets()
    > >> Dim sht As Object
    > >> Application.DisplayAlerts = False
    > >> For Each sht In ThisWorkbook.Sheets
    > >> If sht.Type = 3 Then sht.Delete
    > >> Next sht
    > >> Application.DisplayAlerts = True
    > >> End Sub
    > >>
    > >> --
    > >> HTH
    > >> Nick Hodge
    > >> Microsoft MVP - Excel
    > >> Southampton, England
    > >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    > >>
    > >>
    > >> "CLR" <CLR@discussions.microsoft.com> wrote in message
    > >> news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    > >> > Hi All......
    > >> >
    > >> > I am working on a program that creates 37 different Charts. Each gets
    > >> > created as it's own sheet/tab. I never know how many will be
    > >> > created/deleted
    > >> > during the course of a session. When the session is complete, I would
    > >> > like a
    > >> > macro to delete all Chart Tab/sheets, regardless of their "Chart 22" or
    > >> > "Chart 12" numbers which Excel assigns them....I would like it to
    > >> > delete
    > >> > all
    > >> > existing Chart/tab between numbers 1 and 50 inclusive, but NOT
    > >> > non-chart
    > >> > tabs/sheets. Recording the macro, just don't seem to get me
    > >> > there.......any
    > >> > help would be much appreciated.
    > >> >
    > >> > TIA
    > >> > Vaya con Dios,
    > >> > Chuck, CABGx3
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
    >


  11. #11
    Peter T
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    I'll try and explain, and then confuse you further by asking a question of
    my own!

    As with many objects in Excel, different types of sheet can be identified
    with a Type value (a constant). "Worksheets" and "Charts" (ie chart sheets)
    are each collections of specific types of sheets, which in turn are included
    in the overall "Sheets" collection. There are two more "Types" of sheet, and
    yet another one that's not a sheet.

    Try this in a new workbook, and press Ctrl G to see the Immediate window
    (debug view)

    Sub Test()
    Dim i As Byte
    Dim aType(1 To 4) As Long
    Dim sht As Object
    aType(1) = xlWorksheet '-4167
    aType(2) = xlChart '-4109, or is it 3?
    aType(3) = xlExcel4MacroSheet '3
    aType(4) = xlExcel4IntlMacroSheet '4
    Debug.Print "< i >", "Type applied", "Type returned"

    For i = 1 To 4
    Set sht = Sheets.Add(, , , aType(i))
    Debug.Print i, aType(i), sht.Type
    ' Application.DisplayAlerts = False
    ' sht.Delete
    Next
    Application.DisplayAlerts = True
    End Sub

    If you've run this you may guess my question -
    why doesn't Chartsheet.Type return -4109 ?

    And finally, when's a sheet not a sheet - when it's a DialogSheet which
    cannot return sht.type, and would error if attempted.

    Regards,
    Peter T

    "CLR" <CLR@discussions.microsoft.com> wrote in message
    news:5E18FC4A-EF58-40B1-8A61-63C0D72DC37F@microsoft.com...
    > Thanks anyway Nick, but that is totally "Greek" to me...
    >
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    > "Nick Hodge" wrote:
    >
    > > Just run some code to find the type and send it to the debug window
    > >
    > > For each sh in thisworkbook.sheets
    > > Debug.Print sh.type
    > > Next sh
    > >
    > > --
    > > HTH
    > > Nick Hodge
    > > Microsoft MVP - Excel
    > > Southampton, England
    > > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    > >
    > >
    > > "CLR" <croberts@tampabay.rr.com> wrote in message
    > > news:eZZ0WAEIFHA.2924@TK2MSFTNGP15.phx.gbl...
    > > > Sorry about that Nick............I didn't see your post on my

    newsgroup
    > > > until AFTER I had already responded to Shawn's, and then came home

    from
    > > > work.......... yet here at home it shows as coming in BEFORE

    his.......if
    > > > any event, your's works great as well............I asked for 1-50

    because
    > > > I
    > > > thought I wanted to adjust the range from time to time.........both of

    you
    > > > guy's macros delete ALL of the charts, which is fine-ok in this
    > > > case..........if I make 20 charts and delete them manually, the next

    chart
    > > > comes up #21, and if I go ahead then and make another 30 then I'm up

    to
    > > > Chart #50 but only have 30.........that's why I asked the question the

    way
    > > > I
    > > > did........thought I could take it out well beyond the range I might
    > > > use.......but deleting them all works too.......It's interesting to

    see
    > > > two
    > > > different versions of code to do the same thing..........BTW, "where"

    does
    > > > one find out that "sht. Type=3" is applicable in this case?
    > > >
    > > > Thanks again,
    > > > Vaya con Dios,
    > > > Chuck, CABGx3
    > > >
    > > >
    > > >
    > > >
    > > > "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in

    message
    > > > news:u10Gh3CIFHA.1280@TK2MSFTNGP09.phx.gbl...
    > > >> Chuck
    > > >>
    > > >> Your question is a little ambiguous as you say it creates 37charts

    but
    > > >> you
    > > >> want to delete 1 to 50. If it is ALL chart sheets you want to delete

    the
    > > >> code below will do it
    > > >>
    > > >> Sub DeleteChartSheets()
    > > >> Dim sht As Object
    > > >> Application.DisplayAlerts = False
    > > >> For Each sht In ThisWorkbook.Sheets
    > > >> If sht.Type = 3 Then sht.Delete
    > > >> Next sht
    > > >> Application.DisplayAlerts = True
    > > >> End Sub
    > > >>
    > > >> --
    > > >> HTH
    > > >> Nick Hodge
    > > >> Microsoft MVP - Excel
    > > >> Southampton, England
    > > >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    > > >>
    > > >>
    > > >> "CLR" <CLR@discussions.microsoft.com> wrote in message
    > > >> news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    > > >> > Hi All......
    > > >> >
    > > >> > I am working on a program that creates 37 different Charts. Each

    gets
    > > >> > created as it's own sheet/tab. I never know how many will be
    > > >> > created/deleted
    > > >> > during the course of a session. When the session is complete, I

    would
    > > >> > like a
    > > >> > macro to delete all Chart Tab/sheets, regardless of their "Chart

    22" or
    > > >> > "Chart 12" numbers which Excel assigns them....I would like it to
    > > >> > delete
    > > >> > all
    > > >> > existing Chart/tab between numbers 1 and 50 inclusive, but NOT
    > > >> > non-chart
    > > >> > tabs/sheets. Recording the macro, just don't seem to get me
    > > >> > there.......any
    > > >> > help would be much appreciated.
    > > >> >
    > > >> > TIA
    > > >> > Vaya con Dios,
    > > >> > Chuck, CABGx3
    > > >> >
    > > >> >
    > > >>
    > > >>
    > > >
    > > >

    > >
    > >
    > >




  12. #12
    CLR
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    Hi Peter......
    Thanks for trying, but I'm not far enough along yet for that stuff to make
    any sense to me.......I went, I saw, and it still didn't register. I'm just
    at the stage where I'm recording, copying, and editing my macros. I don't
    understand what they do, but I'm tickled when they do-do <g>. Anyway, I do
    appreciate your efforts.
    Thanks again,
    Vaya con Dios,
    Chuck, CABGx3


    "Peter T" wrote:

    > I'll try and explain, and then confuse you further by asking a question of
    > my own!
    >
    > As with many objects in Excel, different types of sheet can be identified
    > with a Type value (a constant). "Worksheets" and "Charts" (ie chart sheets)
    > are each collections of specific types of sheets, which in turn are included
    > in the overall "Sheets" collection. There are two more "Types" of sheet, and
    > yet another one that's not a sheet.
    >
    > Try this in a new workbook, and press Ctrl G to see the Immediate window
    > (debug view)
    >
    > Sub Test()
    > Dim i As Byte
    > Dim aType(1 To 4) As Long
    > Dim sht As Object
    > aType(1) = xlWorksheet '-4167
    > aType(2) = xlChart '-4109, or is it 3?
    > aType(3) = xlExcel4MacroSheet '3
    > aType(4) = xlExcel4IntlMacroSheet '4
    > Debug.Print "< i >", "Type applied", "Type returned"
    >
    > For i = 1 To 4
    > Set sht = Sheets.Add(, , , aType(i))
    > Debug.Print i, aType(i), sht.Type
    > ' Application.DisplayAlerts = False
    > ' sht.Delete
    > Next
    > Application.DisplayAlerts = True
    > End Sub
    >
    > If you've run this you may guess my question -
    > why doesn't Chartsheet.Type return -4109 ?
    >
    > And finally, when's a sheet not a sheet - when it's a DialogSheet which
    > cannot return sht.type, and would error if attempted.
    >
    > Regards,
    > Peter T
    >
    > "CLR" <CLR@discussions.microsoft.com> wrote in message
    > news:5E18FC4A-EF58-40B1-8A61-63C0D72DC37F@microsoft.com...
    > > Thanks anyway Nick, but that is totally "Greek" to me...
    > >
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > > "Nick Hodge" wrote:
    > >
    > > > Just run some code to find the type and send it to the debug window
    > > >
    > > > For each sh in thisworkbook.sheets
    > > > Debug.Print sh.type
    > > > Next sh
    > > >
    > > > --
    > > > HTH
    > > > Nick Hodge
    > > > Microsoft MVP - Excel
    > > > Southampton, England
    > > > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    > > >
    > > >
    > > > "CLR" <croberts@tampabay.rr.com> wrote in message
    > > > news:eZZ0WAEIFHA.2924@TK2MSFTNGP15.phx.gbl...
    > > > > Sorry about that Nick............I didn't see your post on my

    > newsgroup
    > > > > until AFTER I had already responded to Shawn's, and then came home

    > from
    > > > > work.......... yet here at home it shows as coming in BEFORE

    > his.......if
    > > > > any event, your's works great as well............I asked for 1-50

    > because
    > > > > I
    > > > > thought I wanted to adjust the range from time to time.........both of

    > you
    > > > > guy's macros delete ALL of the charts, which is fine-ok in this
    > > > > case..........if I make 20 charts and delete them manually, the next

    > chart
    > > > > comes up #21, and if I go ahead then and make another 30 then I'm up

    > to
    > > > > Chart #50 but only have 30.........that's why I asked the question the

    > way
    > > > > I
    > > > > did........thought I could take it out well beyond the range I might
    > > > > use.......but deleting them all works too.......It's interesting to

    > see
    > > > > two
    > > > > different versions of code to do the same thing..........BTW, "where"

    > does
    > > > > one find out that "sht. Type=3" is applicable in this case?
    > > > >
    > > > > Thanks again,
    > > > > Vaya con Dios,
    > > > > Chuck, CABGx3
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in

    > message
    > > > > news:u10Gh3CIFHA.1280@TK2MSFTNGP09.phx.gbl...
    > > > >> Chuck
    > > > >>
    > > > >> Your question is a little ambiguous as you say it creates 37charts

    > but
    > > > >> you
    > > > >> want to delete 1 to 50. If it is ALL chart sheets you want to delete

    > the
    > > > >> code below will do it
    > > > >>
    > > > >> Sub DeleteChartSheets()
    > > > >> Dim sht As Object
    > > > >> Application.DisplayAlerts = False
    > > > >> For Each sht In ThisWorkbook.Sheets
    > > > >> If sht.Type = 3 Then sht.Delete
    > > > >> Next sht
    > > > >> Application.DisplayAlerts = True
    > > > >> End Sub
    > > > >>
    > > > >> --
    > > > >> HTH
    > > > >> Nick Hodge
    > > > >> Microsoft MVP - Excel
    > > > >> Southampton, England
    > > > >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    > > > >>
    > > > >>
    > > > >> "CLR" <CLR@discussions.microsoft.com> wrote in message
    > > > >> news:8723B887-2EA7-4144-BDEF-B745F7B4774B@microsoft.com...
    > > > >> > Hi All......
    > > > >> >
    > > > >> > I am working on a program that creates 37 different Charts. Each

    > gets
    > > > >> > created as it's own sheet/tab. I never know how many will be
    > > > >> > created/deleted
    > > > >> > during the course of a session. When the session is complete, I

    > would
    > > > >> > like a
    > > > >> > macro to delete all Chart Tab/sheets, regardless of their "Chart

    > 22" or
    > > > >> > "Chart 12" numbers which Excel assigns them....I would like it to
    > > > >> > delete
    > > > >> > all
    > > > >> > existing Chart/tab between numbers 1 and 50 inclusive, but NOT
    > > > >> > non-chart
    > > > >> > tabs/sheets. Recording the macro, just don't seem to get me
    > > > >> > there.......any
    > > > >> > help would be much appreciated.
    > > > >> >
    > > > >> > TIA
    > > > >> > Vaya con Dios,
    > > > >> > Chuck, CABGx3
    > > > >> >
    > > > >> >
    > > > >>
    > > > >>
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  13. #13
    Jon Peltier
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    If you're in a real hurry, this is quicker to type:

    Application.DisplayAlerts = False
    ActiveWorkbook.Charts.Delete
    Application.DisplayAlerts = True

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    CLR wrote:

    > I tried to thank you earlier Shawn, but the system refused my reply.......
    >
    > Your code works FINE, exactly what I wanted..........(and in a hurry
    > too).....<g>
    >
    > Many many thanks,
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    >
    > "Shawn O'Donnell" wrote:
    >
    >
    >>"CLR" wrote:
    >>
    >>>I would like a macro to delete all Chart Tab/sheets, regardless of
    >>>their "Chart 22" or "Chart 12" numbers which Excel assigns them

    >>
    >>Stand-alone chart sheets are kept in a collection called "Charts" that
    >>belongs to Workbook objects like ActiveWorkbook. You can step through the
    >>collection and delete each sheet, if that's what you really want to do...
    >>
    >>Here's a no-going-back macro. If you want to at least think about each
    >>sheet for a second, you can comment out the DisableAlert lines.
    >>
    >>Sub deleteAllChartSheets()
    >> Dim doomedChart As Variant
    >> Application.DisplayAlerts = False
    >> For Each doomedChart In ActiveWorkbook.Charts
    >> doomedChart.Delete
    >> Next doomedChart
    >> Application.DisplayAlerts = True
    >>End Sub
    >>



  14. #14
    CLR
    Guest

    Re: Deleting multiple Chart Tabs/sheets

    Thanks Jon..........it looks so simple when you do it <g>

    Vaya con Dios,
    Chuck, CABGx3


    "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
    news:eJdNr0fIFHA.2984@TK2MSFTNGP15.phx.gbl...
    > If you're in a real hurry, this is quicker to type:
    >
    > Application.DisplayAlerts = False
    > ActiveWorkbook.Charts.Delete
    > Application.DisplayAlerts = True
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    >
    > CLR wrote:
    >
    > > I tried to thank you earlier Shawn, but the system refused my

    reply.......
    > >
    > > Your code works FINE, exactly what I wanted..........(and in a hurry
    > > too).....<g>
    > >
    > > Many many thanks,
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > >
    > > "Shawn O'Donnell" wrote:
    > >
    > >
    > >>"CLR" wrote:
    > >>
    > >>>I would like a macro to delete all Chart Tab/sheets, regardless of
    > >>>their "Chart 22" or "Chart 12" numbers which Excel assigns them
    > >>
    > >>Stand-alone chart sheets are kept in a collection called "Charts" that
    > >>belongs to Workbook objects like ActiveWorkbook. You can step through

    the
    > >>collection and delete each sheet, if that's what you really want to

    do...
    > >>
    > >>Here's a no-going-back macro. If you want to at least think about each
    > >>sheet for a second, you can comment out the DisableAlert lines.
    > >>
    > >>Sub deleteAllChartSheets()
    > >> Dim doomedChart As Variant
    > >> Application.DisplayAlerts = False
    > >> For Each doomedChart In ActiveWorkbook.Charts
    > >> doomedChart.Delete
    > >> Next doomedChart
    > >> Application.DisplayAlerts = True
    > >>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