Deleting multiple Chart Tabs/sheets

[Guest]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Peter T]

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

Regards,
Peter T

 

[Nick Hodge]

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
(e-mail address removed)

 

[CLR]

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]

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
(e-mail address removed)

 

[Guest]

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

Vaya con Dios,
Chuck, CABGx3

 

[Peter T]

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

 

[Guest]

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

 

[Jon Peltier]

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]

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

Vaya con Dios,
Chuck, CABGx3