| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting multiple Chart Tabs/sheets
To be extremely pedantic, sorry <g, sht.Type = 3 could also be Macro sheet.
Regards, Peter T "Nick Hodge" wrote in message ... 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 HIS "CLR" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 HIS "Peter T" <peter_t@discussions wrote in message ... To be extremely pedantic, sorry <g, sht.Type = 3 could also be Macro sheet. Regards, Peter T "Nick Hodge" wrote in message ... 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 HIS "CLR" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 HIS "CLR" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 HIS "CLR" wrote in message ... 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" wrote in message ... 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 HIS "CLR" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 HIS "CLR" wrote in message ... 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" wrote in message ... 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 HIS "CLR" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 HIS "CLR" wrote in message ... 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" wrote in message ... 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 HIS "CLR" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 HIS "CLR" wrote in message ... 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" wrote in message ... 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 HIS "CLR" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting multiple Chart Tabs/sheets
Thanks Jon..........it looks so simple when you do it <g
Vaya con Dios, Chuck, CABGx3 "Jon Peltier" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why do the tabs disappear (workbook has multiple sheets) | Excel Discussion (Misc queries) | |||
| Chart on multiple sheets | Charts and Charting in Excel | |||
| Deleting Duplicate Records across multiple sheets | Excel Worksheet Functions | |||
| deleting columns on multiple sheets | Excel Discussion (Misc queries) | |||
| building chart from multiple sheets | Charts and Charting in Excel | |||
Linear Mode
