DESPERATELY NEED HELP: How to delete an old graph and replace with a new one [VBA Macro]

[GKarl]

Hi all,

I'm currently working on market research and data analysis for my company, which specializes in fashion products.

I am trying to generate two different macro buttons that generate graphs in a demarcated space for the below data, one graph for product type and one graph for style.

________________________
[TABLE="width: 418"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Product Type[/TD]
[TD]Quantity Sold[/TD]
[TD]Total Revenue[/TD]
[TD]Total Cost[/TD]
[TD]Total Profit[/TD]
[/TR]
[TR]
[TD]Dresses[/TD]
[TD]87[/TD]
[TD]8436.6[/TD]
[TD]2846[/TD]
[TD]5590.6[/TD]
[/TR]
[TR]
[TD]Tops[/TD]
[TD]58[/TD]
[TD]5630.4[/TD]
[TD]2210[/TD]
[TD]3420.4[/TD]
[/TR]
[TR]
[TD]Pants[/TD]
[TD]82[/TD]
[TD]8505[/TD]
[TD]3055.6[/TD]
[TD]5449.4[/TD]
[/TR]
[TR]
[TD]Shorts[/TD]
[TD]57[/TD]
[TD]5659.2[/TD]
[TD]2102.4[/TD]
[TD]3556.8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Style Analysis[/TD]
[/TR]
[TR]
[TD]Style[/TD]
[TD]Quantity Sold[/TD]
[TD]Total Revenue[/TD]
[TD]Total Cost[/TD]
[TD]Total Profit[/TD]
[/TR]
[TR]
[TD]Glamorous[/TD]
[TD]79[/TD]
[TD]7311.6[/TD]
[TD]2754.6[/TD]
[TD]4557[/TD]
[/TR]
[TR]
[TD]Natural[/TD]
[TD]69[/TD]
[TD]7059.6[/TD]
[TD]2220.8[/TD]
[TD]4838.8[/TD]
[/TR]
[TR]
[TD]Dramatic[/TD]
[TD]59[/TD]
[TD]5743.8[/TD]
[TD]2220.6[/TD]
[TD]3523.2[/TD]
[/TR]
[TR]
[TD]Street[/TD]
[TD]77[/TD]
[TD]8116.2[/TD]
[TD]3018[/TD]
[TD]5098.2[/TD]
[/TR]
</tbody>[/TABLE]

________________________
ERROR BOLDED

Sub BrandGraph()
'
' BrandGraph Macro
'
If ActiveChart = "Chart1" Then
Charts("Chart1").Delete
Else


ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Range("'Profit Analysis'!$A$10:$E$15")
ActiveChart.SeriesCollection(1).Select
Selection.Delete
Charts.Add.Name = "Chart1"

End If




End Sub

So what the code does above, is basically, when I click the button for it, it is supposed to delete the old graph in that space, and generate a new one based on its data range, i.e. I click "Brand" and the old graph, whatever is in the demarcated space, deletes, and a new Brand analysis graph shows up, and I click "Style" and the old Brand graph deletes and the "Style" graph takes its place instead.

I have the code that generates a new graph - but I can't figure out the code to get the existing graph to delete itself. Can someone help me fix the problem, or hopefully help me come up with code to do all that above?

YOUR HELP IS GREATLY APPRECIATED. I will shower you with positive energy and beautiful praise.

Thank you very very much!

 

[Jon Peltier]

I wonder if this:

If ActiveChart = "Chart1" Then
    Charts("Chart1").Delete
Else

would work if you adjusted it to this:

If ActiveChart.Parent.Name = "Chart1" Then
  ActiveChart.Delete
Else

But using a chart object name (chart object is the parent shape of a chart) is error prone, because you never really know what name Excel gives to a new chart.

You could just assume that the relevant chart is selected when the macro is run, and if it isn't, remind the user to select a chart:

If ActiveChart Is Nothing Then
    MsgBox "Select the chart to delete, then try again."
Else
    ActiveChart.Parent.Delete
    ' code to replace/recreate the chart
End If