I have a spreadsheet and am copying the spreadsheet and updating the 7 charts on the sheet with data on this new sheet. No problems here and has been working for some six months. came back today after my christmas break and the charts are now causing errors. Code is below.
My question is where does excel assign the chart name, as I think it has corrupted as the data is being assigned to the wrong chart. any help would be gratefully appreciated.
The error i get is: Method 'Seriescollection' of object '_chart' failed
Code
'*** Change series for charts
'*** AGE STRUCTURE CHART 1
Set chtActiveChart = wksNewSheet.ChartObjects("Chart 3").Chart
chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R8C3:R8C10," & strSheetName & "!R12C3:R12C10,1)"
chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R13C3:R13C10,2)"
'*** TENURE - CHART 2
Set chtActiveChart = wksNewSheet.ChartObjects("Chart 1").Chart
chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R31C5:R35C5,2)"
chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R31C1:R35C1," & strSheetName & "!R31C4:R35C4,1)"
'*** General health - CHART 3
Set chtActiveChart = wksNewSheet.ChartObjects("Chart 4").Chart
chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R51C5:R53C5,2)"
chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R51C1:R53C1," & strSheetName & "!R51C4:R53C4,1)"
'*** Economic Activity - CHART 4
Set chtActiveChart = wksNewSheet.ChartObjects("Chart 2").Chart
chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R77C1:R81C1," & strSheetName & "!R77C4:R81C4,1)"
'*** Economic Inactivity - CHART 5
Set chtActiveChart = wksNewSheet.ChartObjects("Chart 5").Chart
chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R87C1:R91C1," & strSheetName & "!R87C4:R91C4,1)"
'*** Occupation - CHART 6
Set chtActiveChart = wksNewSheet.ChartObjects("Chart 6").Chart
chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R101C5:R109C5,2)"
chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R101C2:R109C2," & strSheetName & "!R101C4:R109C4,1)"
'*** Industry - CHART 7
Set chtActiveChart = wksNewSheet.ChartObjects("Chart 8").Chart
chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R115C5:R125C5,2)"
chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R115C2:R125C2," & strSheetName & "!R115C4:R125C4,1)"