Worksheet Loop Macro Using InStr, maybe

[SBF12345]

Greetings,

I would like to build a simple macro to delete charts in the activeworkbook. I have the following written below:

Dim x As Worksheet

    For Each x In ActiveWorkbook.Worksheets


        If InStr(1, ActiveWorkbook.Worksheet(x).Name, "Chart", 1) Then


        ActiveWorkbook.Worksheet(x).Delete


        End If


    Next x

I'm not sure that InStr is the correct function here. I would like a true or false value to return whether the word "Chart" is or is not present in the name. It should always be the first 5 characters of the worksheet name

 

[Dryver14]

Sorry struggling to paste onto the sheet so I typed it


sub GetRid()

Dim x as worksheet
dim y as string
for each x in activeworkbook.sheets
y = left(activesheet.name,5)
application.display alerts = false
if y = "Chart" Then Activesheet.delete
application.displayalerts = true
next x

end sub

 

[SBF12345]

Great Thanks!

 

[Dryver14]

Your welcome

 

[SBF12345]

Hi, I am having trouble running the script below. For some reason it doesn't seem to want to run through the sheets. Can you see anything that might be preventing the macro from stepping through the worksheets?

Dim x As WorksheetDim y As String


For Each x In ActiveWorkbook.Worksheets


    y = Left(ActiveSheet.Name, 5)


    If y = "Chart" Then
    
    ActiveSheet.Delete
    
    End If


Next x




'
End Sub

 

[shg]

Are these chart sheets that you're trying to delete, or worksheets that start with "Chart"?

 

[SBF12345]

They have tabs and are kept alongside the worksheets. They were generated using the chart wizard with the last choice being "chart in new sheet". To answer your question, I'm not sure.

 

[shg]

Sub sbf()
  Dim sh As Object

  Application.DisplayAlerts = False
  For Each sh In Sheets
    If TypeOf sh Is Chart Then sh.Delete
  Next sh
End Sub

 

[SBF12345]

Great it runs and works! Thanks shg!

 

[shg]

Y'er welcome.