How to delete two charts both located in a new sheet?

Hi Rory

The code above does not work....could you please give it another try?

Thanks

If you meant that both charts are embedded in a sheet to the left of the specified one, then:

Sub DeleteCharts()
    Dim objSheet, n As Long
    On Error Resume Next
    Application.DisplayAlerts = False
    Set objSheet = Sheets(Sheets("Macro Sheet").Index - 1)
    If objSheet.chartobjects.Count > 0 Then objSheet.Delete
    Application.DisplayAlerts = True
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

sorry but the code does not work...let me explain a bit more clearly.

I have a macro that generates two charts...they are located in sheet "chart1" and "chart2" respectively (so not as a chart in the "macro" sheet itself). I basically want these sheets to be deleted the next time I run the macro as another 2 new charts will be produced.

Thanks

That is what the original code should do assuming they are directly to the left of the specified macro worksheet (with no hidden sheets in between either). Try commenting out the On Error Resume Next line and then run it again.

Thing is the sheets next to the "Macro Sheet" will be called something different each time, so it complicates things a bit.

What is the code to simply just delete the two sheets (or just one sheet and I will just repeat the line) next to the "MacroSheet"?I tried to do a macro record but it comes up with a box asking if you are sure if you want to delete it.

Thanks
Ybui

That is what the code does. It doesn't care what they are called. Did you follow my last suggestion?

O nice one...sorry I didnt look at it properly

Thanks

Is there of way of specifying...
IF the sheets to the left of "Macro Sheet" are Chart sheets, then delete?

Thanks

and what if there are no sheets to the left of "Macro Sheet"??It comes up with an error if this is the case...how could I correct for this?

As originally written, the code will delete the two sheets to the left of the macro sheet, if they are both chart sheets. It would not error as it has an error handler. The only situation I can think of that it wouldn't handle would be if the sheet immediately to the left is not a chart sheet - then it won't delete anything.

oh sorry mate...don't know what I been thinking...yes it original code works...for your troubles im going to raise the points

Thanks

Sorry mate...but the code cannot handle it if there are no sheets to the left of "Macro Sheet".

How can this be fixed?
Thanks

If you are getting errors in spite of the On Error Resume Next statement, then you must have your VBEditor set to break on all errors, in which case you can turn that off under Tools-Options in the VBEditor.