Delete All but specified Charts in Wkbk

D

DynamiteSkippy

I am trying to loop through all charts in a workbook and delete all but 2
charts specified by a variable name.

Below is my entire code... I am specifically working on the second for loop.
THe problem I am trying to resolve is that it deletes all charts irregardless
of name.

Sub DelSheets()
Dim i As Integer
Dim Wks
Dim Cht
Dim NoGo1, NoGo2 As String

PraSpecialty = "CARDIO"
For Each Wks In ThisWorkbook.Worksheets

'MsgBox prompt:="Wks" & " " & Wks.Name

If Left(Wks.Name, 5) = "Sheet" Then
Application.DisplayAlerts = False
Wks.Delete
Application.DisplayAlerts = True
Else
'MsgBox "Sorry, you cannot delete this sheet"
End If

Next Wks

For Each Cht In ThisWorkbook.Charts
NoGo1 = PraSpecialty & "_ChartList"
NoGo2 = PraSpecialty & "_Chart"
If Cht.Name <> NoGo1 Or Cht.Name <> NoGo2 Then
Application.DisplayAlerts = False
'Cht.Delete
MsgBox "DELETE->" & Cht.Name
Application.DisplayAlerts = True
Else
MsgBox Cht.Name
'MsgBox "Sorry, you cannot delete this chart"
End If
Next Cht
MsgBox "Wait a tick... Is this working?"
End Sub
 
P

Peter T

Assuming you want to delete all Worksheets named Sheet* and all chart sheets
not named "CARDIO_ChartList" nor "CARDIO_ChartList"

Sub DelSheets2()
Dim i As Long
Dim Wks As Worksheet
Dim Cht As Chart
Dim NoGo1 As String, NoGo2 As String
Const PraSpecialty As String = "CARDIO"

NoGo1 = PraSpecialty & "_ChartList"
NoGo2 = PraSpecialty & "_Chart"

Application.DisplayAlerts = False

On Error GoTo errH
For Each Wks In ThisWorkbook.Worksheets

If Left(Wks.Name, 5) = "Sheet" Then
Wks.Delete
Else
'MsgBox "Sorry, you cannot delete this sheet", , Wks.Name
End If

Next Wks

For Each Cht In ThisWorkbook.Charts

If (Cht.Name <> NoGo1) And (Cht.Name <> NoGo2) Then
Cht.Delete
Else
'MsgBox "Sorry, you cannot delete this chart", , Cht.Name
End If
Next Cht
errH:
Application.DisplayAlerts = True
'MsgBox "Wait a tick... Is this working?"
End Sub

Note a workbook must always contain at least one sheet,

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top