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

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

That's a good question. I don't understnad the embedded chart object model.
How would I know which chartobject corresponds to my chart? I chose
chartobjects(1) because I only have one chart. But I have no way of knowing
whether my chart has a different index number, at least not as far as I can
tell.

At any rate, the code you used isn't working for me. Maybe my chart has some
other index number, but how can I know what it is?

"Jon Peltier" wrote:

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

If you have one chart, then it is ChartObjects(1). You can see a chart
object's name by holding shift while clicking on the chart, then looking in
the name box (above the row numbers).

To list all of the charts, go to the VB Editor and open the Immediate
Window. Paste this line, click the cursor back into the line, and press
enter:

for i = 1 to activesheet.chartobjects.count : ? i,
activesheet.chartobjects(i).name : next

this gives you a numerical list of the chart objects in the sheet along with
their names.

You can get the active chart's name by using this line in the Immediate
Window:

? activechart.parent.name

Has the series formula of the chart been updated to reflect the new source
data sheet?

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


"Rayo K" wrote in message
...
That's a good question. I don't understnad the embedded chart object
model.
How would I know which chartobject corresponds to my chart? I chose
chartobjects(1) because I only have one chart. But I have no way of
knowing
whether my chart has a different index number, at least not as far as I
can
tell.

At any rate, the code you used isn't working for me. Maybe my chart has
some
other index number, but how can I know what it is?

"Jon Peltier" wrote:

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

It still does not work. The sourcedata remains unchanged. When I tried the
code for hte immediate window, it gave an error saying there was a "Next
without for" error.

When I tried entering "activesheet.chartobjects.count" it said the count
method failed.

"Jon Peltier" wrote:

If you have one chart, then it is ChartObjects(1). You can see a chart
object's name by holding shift while clicking on the chart, then looking in
the name box (above the row numbers).

To list all of the charts, go to the VB Editor and open the Immediate
Window. Paste this line, click the cursor back into the line, and press
enter:

for i = 1 to activesheet.chartobjects.count : ? i,
activesheet.chartobjects(i).name : next

this gives you a numerical list of the chart objects in the sheet along with
their names.

You can get the active chart's name by using this line in the Immediate
Window:

? activechart.parent.name

Has the series formula of the chart been updated to reflect the new source
data sheet?

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


"Rayo K" wrote in message
...
That's a good question. I don't understnad the embedded chart object
model.
How would I know which chartobject corresponds to my chart? I chose
chartobjects(1) because I only have one chart. But I have no way of
knowing
whether my chart has a different index number, at least not as far as I
can
tell.

At any rate, the code you used isn't working for me. Maybe my chart has
some
other index number, but how can I know what it is?

"Jon Peltier" wrote:

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

Something's wrong. Even if there are no charts, ChartObjects.Count should
return zero.

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


"Rayo K" wrote in message
...
It still does not work. The sourcedata remains unchanged. When I tried the
code for hte immediate window, it gave an error saying there was a "Next
without for" error.

When I tried entering "activesheet.chartobjects.count" it said the count
method failed.

"Jon Peltier" wrote:

If you have one chart, then it is ChartObjects(1). You can see a chart
object's name by holding shift while clicking on the chart, then looking
in
the name box (above the row numbers).

To list all of the charts, go to the VB Editor and open the Immediate
Window. Paste this line, click the cursor back into the line, and press
enter:

for i = 1 to activesheet.chartobjects.count : ? i,
activesheet.chartobjects(i).name : next

this gives you a numerical list of the chart objects in the sheet along
with
their names.

You can get the active chart's name by using this line in the Immediate
Window:

? activechart.parent.name

Has the series formula of the chart been updated to reflect the new
source
data sheet?

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


"Rayo K" wrote in message
...
That's a good question. I don't understnad the embedded chart object
model.
How would I know which chartobject corresponds to my chart? I chose
chartobjects(1) because I only have one chart. But I have no way of
knowing
whether my chart has a different index number, at least not as far as I
can
tell.

At any rate, the code you used isn't working for me. Maybe my chart has
some
other index number, but how can I know what it is?

"Jon Peltier" wrote:

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

It still does not work. The sourcedata remains unchanged. When I tried the
code for hte immediate window, it gave an error saying there was a "Next
without for" error.

Did you unwrap the two lines bfore pressing enter.

When I tried entering "activesheet.chartobjects.count" it said the count
method failed.

Did you preceed with a ?

(I havn't looked at your original problem)

Regards,
Peter T

"Rayo K" wrote in message
...
It still does not work. The sourcedata remains unchanged. When I tried the
code for hte immediate window, it gave an error saying there was a "Next
without for" error.

When I tried entering "activesheet.chartobjects.count" it said the count
method failed.

"Jon Peltier" wrote:

If you have one chart, then it is ChartObjects(1). You can see a chart
object's name by holding shift while clicking on the chart, then looking
in
the name box (above the row numbers).

To list all of the charts, go to the VB Editor and open the Immediate
Window. Paste this line, click the cursor back into the line, and press
enter:

for i = 1 to activesheet.chartobjects.count : ? i,
activesheet.chartobjects(i).name : next

this gives you a numerical list of the chart objects in the sheet along
with
their names.

You can get the active chart's name by using this line in the Immediate
Window:

? activechart.parent.name

Has the series formula of the chart been updated to reflect the new
source
data sheet?

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


"Rayo K" wrote in message
...
That's a good question. I don't understnad the embedded chart object
model.
How would I know which chartobject corresponds to my chart? I chose
chartobjects(1) because I only have one chart. But I have no way of
knowing
whether my chart has a different index number, at least not as far as
I
can
tell.

At any rate, the code you used isn't working for me. Maybe my chart
has
some
other index number, but how can I know what it is?

"Jon Peltier" wrote:

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