This worked for me (I changed the range to match my example):

Sub ChangeSourceSheet()

Dim ws1 As Worksheet
Dim ch1 As Chart

For Each ws1 In Worksheets

Set ch1 = ws1.ChartObjects.Item(1).Chart
ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _
:=xlColumns

Next

End Sub

Are you sure you don't need to change ChartObjects(2)?

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


"Rayo K" wrote in message
...
I have a spreadsheet with ~120 sheets each with one chart. I recently
modified the chart and copied the new chart to each sheet using VB to
copy.
unfortunately, I realized that the sourcedata is the same sheet for all
charts. I now want to change the source data for each sheet to the range
below.

This code executes but when i check the charts, the sourcedata still
refers
to the 1st sheet. What am I doing wrong?

For each ws1 in Worksheets

Set ch1 = ws1.ChartObjects.Item(1).Chart
ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _
:=xlColumns