Search code examples
vbaexcelexcel-2016excel-charts

Unable to change chart references with recorded macro due to 1004 run-time error

I am trying to create a macro that will rename a tab and then change each chart's source data to a named range. I recorded a macro by running through each chart and changing the source data manually and got the below code (cleaned up by removing Application.CutCopyMode = False and similar code):

Sub UpdateRanges()
' UpdateRanges Macro
' Keyboard Shortcut: Ctrl+Shift+J
ActiveSheet.Name = "New"
ActiveSheet.ChartObjects("ProfSKU").Activate
    ActiveChart.FullSeriesCollection(1).Values = "==New!EBITDA_Margin"
    ActiveChart.FullSeriesCollection(2).Values = "==New!Gross_Margin"
ActiveSheet.ChartObjects("Pareto").Activate
    ActiveChart.FullSeriesCollection(1).Values = "==New!Pareto_Revenue"
    ActiveChart.FullSeriesCollection(2).Values = "==New!Pareto_EBITDA"
    ActiveChart.FullSeriesCollection(3).Values = "==New!Pareto_Volume"
    ActiveChart.FullSeriesCollection(4).Values = "==New!Pareto"
ActiveSheet.ChartObjects("UnitMaterials").Activate
    ActiveChart.FullSeriesCollection(1).Values = "==New!Unit_Materials_Desc"
ActiveSheet.ChartObjects("UnitManu").Activate
    ActiveChart.FullSeriesCollection(1).Values = "==New!Unit_Manufacturing_Desc"
ActiveSheet.ChartObjects("UnitSGA").Activate
    ActiveChart.FullSeriesCollection(1).Values = "==New!Unit_SGA_Desc"
ActiveSheet.ChartObjects("UnitEBITDA").Activate
    ActiveChart.FullSeriesCollection(1).Values = "==New!Unit_EBITDA_Desc"
ActiveSheet.ChartObjects("SKUCostStruc").Activate
    ActiveChart.FullSeriesCollection(1).Values = "==New!Unit_EBITDA"
    ActiveChart.FullSeriesCollection(2).Values = "==New!Unit_SGA"
    ActiveChart.FullSeriesCollection(3).Values = "==New!Unit_Manufacturing"
    ActiveChart.FullSeriesCollection(4).Values = "==New!Unit_Materials"
ActiveSheet.ChartObjects("SKUCostStruc100").Activate
    ActiveChart.Parent.Delete
ActiveSheet.ChartObjects("SKUCostStruc").Activate
    ActiveChart.ChartArea.Copy
    Range("AI76").Select
    ActiveSheet.Paste
    ActiveChart.Name = "SKUCostStruc100"
ActiveSheet.ChartObjects("SKUCostStruc100").Activate
    ActiveChart.ChartType = xlColumnStacked100
End Sub

Note that I have named the charts in case I need to move them around the worksheet.

I receive "Run-time error 1004: Application defined or object-defined error" when the macro hits the first chart data source change line:

ActiveChart.FullSeriesCollection(1).Values = "==New!EBITDA_Margin"

If I then run

ActiveChart.FullSeriesCollection(1).Values

(with the "ProfSKU" chart selected) through the Immediate window VBA throws "Run-time error 13: Type Mismatch".

I'm not sure why this would be getting this error considering it's the exact same code generated when I recorded doing it manually. Why might this be happening?

Solution

  • @ShaiRado removing the second = sign was all that was needed. And my reference to chart name Pareto returned an error because that was a named range while the chart was called ParetoChart. Strange that the macro recorder would add an erroneous second =. Thanks for the help.