software

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 quot;importquot; all of this
info in one fell swoop. Can someone assist me with the code?

Thanks in advance,
Barb Reinhardt

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(quot;A1quot;, .Range(quot;Aquot; amp; .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 = quot;='quot; amp; strSheetName amp; quot;'!quot; amp; strDataNR
.XValues = quot;='quot; amp; strSheetName amp; quot;'!quot; amp; strLabelNR
End With
End With
Next

End Sub

Cheers
Andy

Barb Reinhardt wrote:
gt; I have a worksheet with the following in a list:
gt;
gt; Column A: Worksheet name
gt; Column B: chart name (i.e. Chart 1, Chart 3, Chart 9)
gt; Column C: Series number in the chart
gt; Column N: X values for series (as a named range)
gt; Column J: Y values for series (as a named range)
gt;
gt; Row 2 contains the first data entry.
gt;
gt; I'd like to modify all of the series in the workbook so that it contains the
gt; values on this worksheet. Actually, I'm trying to get it set up to have
gt; dynamic charts so that we plot only the last 12 months worth of data. I've
gt; got the names figured out so that works, but want to quot;importquot; all of this
gt; info in one fell swoop. Can someone assist me with the code?
gt;
gt; Thanks in advance,
gt; Barb Reinhardt

--

Andy Pope, Microsoft MVP - Excel
www.andypope.info

Barb -

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

peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
PeltierTech.com/
_______quot;Barb Reinhardtquot; gt; wrote in message
...
gt;I have a worksheet with the following in a list:
gt;
gt; Column A: Worksheet name
gt; Column B: chart name (i.e. Chart 1, Chart 3, Chart 9)
gt; Column C: Series number in the chart
gt; Column N: X values for series (as a named range)
gt; Column J: Y values for series (as a named range)
gt;
gt; Row 2 contains the first data entry.
gt;
gt; I'd like to modify all of the series in the workbook so that it contains
gt; the
gt; values on this worksheet. Actually, I'm trying to get it set up to have
gt; dynamic charts so that we plot only the last 12 months worth of data.
gt; I've
gt; got the names figured out so that works, but want to quot;importquot; all of this
gt; info in one fell swoop. Can someone assist me with the code?
gt;
gt; Thanks in advance,
gt; Barb Reinhardt