Changing series information for approximately 60 series in a workb

[Guest]

I have a worksheet with the following in a list:

Column A: Worksheet name
Column B: chart name (i.e. Chart 1, Chart 3, Chart 9)
Column C: Series number in the chart
Column N: X values for series (as a named range)
Column J: Y values for series (as a named range)

Row 2 contains the first data entry.

I'd like to modify all of the series in the workbook so that it contains the
values on this worksheet. Actually, I'm trying to get it set up to have
dynamic charts so that we plot only the last 12 months worth of data. I've
got the names figured out so that works, but want to "import" all of this
info in one fell swoop. Can someone assist me with the code?

Thanks in advance,
Barb Reinhardt

 

[Andy Pope]

Hi,

Here is a starting point. There is no error trapping in this so if a
sheetname or range name is wrong then it will fail.

Sub Barb()

Dim strSheetName As String
Dim strChartName As String
Dim strLabelNR As String
Dim strDataNR As String
Dim rngInput As Range
Dim rngTemp As Range
Dim objCht As ChartObject
Dim intSeries As Integer

With ActiveSheet
Set rngInput = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With
For Each rngTemp In rngInput
With rngTemp
strSheetName = .Offset(0, 0)
strChartName = .Offset(0, 1)
intSeries = .Offset(0, 2)
strLabelNR = .Offset(0, 13)
strDataNR = .Offset(0, 9)
End With
With Worksheets(strSheetName).ChartObjects(strChartName).Chart
With .SeriesCollection(intSeries)
.Values = "='" & strSheetName & "'!" & strDataNR
.XValues = "='" & strSheetName & "'!" & strLabelNR
End With
End With
Next

End Sub

Cheers
Andy

 

[Jon Peltier]

Barb -

I wrote myself a little add-in for just this sort of problem. You may be
able to make use of it:

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______