Changing chart sources via Macros

[cdegar01]

I have charts on a page like this:

ABCDEF

GHIJKL

Where each letter represents a chart. The X values for charts
BCDEFGHIJKL are the same as A's x values. The Y values for BCDEFGHIJKL
move over one column to the right, and then down a couple rows for G,
and then more over to the right for the remaining charts.

The problem is, when I copy all these charts and then paste them down a
page, they reference the old data. I want to be able to run a macro
that will change the references of all the charts relative to their
positions.

This is the starting code that I have:

Sub SetChartSource()
Dim ws As Worksheet
Dim chObj As ChartObject
Dim r As Long
Set ws = ActiveSheet

For Each chObj In ActiveSheet.ChartObjects
r = chObj.TopLeftCell.Row
chObj.Chart.SetSourceData _
Source:=ws.Range("A" & r & ":B" & r + 7), _
PlotBy:=xlRows
Next chObj
End Sub

I don't know much about this stuff, so thanks for your patience!

 

[Tushar Mehta]

Anyone considering responding might want to check the responses at
http://www.mrexcel.com/board2/viewtopic.php?t=74969

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions