VBA Excel: Deleting all charts and graphs in a workbook, except one

0
votes

I have a macro that generates a lot of worksheets and charts. There's also various subroutines that run so the names and quantity of each worksheet/chart generated is never the same. What is constant is my HOME worksheet which is the UI for the user and I want it to be unaffected.

I found a similar question here about deleting all worksheets except the one you are working with (i.e. HOME). Here's what I have so far.

Sub ZRESET()

Dim ws As Worksheet, wb As Workbook
Set wb = ActiveWorkbook

    Sheets("HOME").Select

    Application.DisplayAlerts = False
        For Each ws In wb.Worksheets
            If ws.Name <> "HOME" Then
                ws.Delete
            End If
            If Chart.Name = "" Then
                Charts.Delete
            End If
        Next
    Application.DisplayAlerts = True

    Range("B5:E5,B9:E9,B13:E13,B14:E14").ClearContents
    Range("A1").Select

End Sub

The worksheets delete fine, the hang up I have is the charts. I tried various attempts to remove charts and sometimes they work (i.e placing Charts.Delete outside of a FOR loop and IF statement). But this requires me to actually have a chart in the workbook. Sometime the user can just develop worksheets but no charts.

Any advice to continue my goal of deleting SHEETS and/or CHARTS, while keeping my HOME sheet intact?

2
vbaexcel
You must refer to the Sheets collection rather than Worksheets if you want to delete charts too (if you mean chart sheets). – SJR
Hello, Josh, are you saying sometimes it deletes the charts on other sheets and sometimes it doesn't ? or there is some sort of error initiated if it doesn't find a chart? – perfo
To add to @SJR post. There are 3 types of sheets in Excel. 1) ** Worksheets (standard tab that loads on startup). **2) Chart Sheets (charts set up as a separate tab. 3) Macro sheets (sheets that only have vba attached. Worksheets collection refers only to worksheets. Sheets collection refers to all sheets, regardless of type. – Scott Holtzman
When I change WS from Worksheet to Sheets, ws.Name results in "Compile error: Method or data member not found"... You are correct that I am trying to delete ChartSheets. – joshjayse

2 Answers

1
votes
Option Explicit

Sub GetRid()
  Dim ASheet As Worksheet
  Dim AChart As Chart


  Application.DisplayAlerts = False
  Application.ScreenUpdating = False

  '** first scan for and delete all non HOME worksheets ***
  For Each ASheet In ActiveWorkbook.Worksheets
    If UCase(ASheet.Name) <> "HOME" Then
      ASheet.Delete
    End If
  Next

  '** Now scan and delete any ChartSheets ****
  For Each AChart In ActiveWorkbook.Charts
    AChart.Delete
  Next

  Application.DisplayAlerts = True
  Application.ScreenUpdating = True

End Sub
0
votes
Option Explicit

Sub AllSheetsAndcharts()

  Dim AChart As ChartObject
  Dim ASheet As Worksheet
  Application.DisplayAlerts = False
  For Each ASheet In ActiveWorkbook.Worksheets
     If UCase(ASheet.Name) <> "HOME" Then

       For Each AChart In ASheet.ChartObjects
         AChart.Delete
       Next
       ASheet.Delete
     End If
  Next
  Application.DisplayAlerts = False

End Sub