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

Question

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?

you have 1 too many = in the beginning, try "=New!EBITDA_Margin". Second, what is EBITDA_Margin ? is it a Named Range in New sheet ? — Shai Rado
...or is is the name of a Chart? Also, it's the same code, but did you remove any code in between? — ashleedawg
I'll try to remove the second = but that is the code the macro recorder generated. All of the charts are referencing named ranges, EBITDA_Margin being one of them. And yes, they are all available to the New worksheet. To the second comment, I removed some code but only Application.CutCopyMode = False and any ActiveWindow.SmallScroll references. Everything about changing chart data sources was left alone (other than indenting to make it more readable). — Peter
When I removed the second = I got "run-time error '-2147024809 (80070057)': the item with the specified name wasn't found." and the debugger referenced the next chart selection line: ActiveSheet.ChartObjects("Pareto").Activate — Peter

Peter Peter · Accepted Answer · 2018-03-18T13:03:47

@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.

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

1 Answers