Changing series information for approximately 60 series in a workb


G

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
 
Ad

Advertisements

A

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top