How do I modify charts thorugh VB
 

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

How do I modify charts thorugh VB
 

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

How do I modify charts thorugh VB
 

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

How do I modify charts thorugh VB
 

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

How do I modify charts thorugh VB
 

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

How do I modify charts thorugh VB
 

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

How do I modify charts thorugh VB
 

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

How do I modify charts thorugh VB
 

OK,

I tried "msgbox sheets("17th-3").chartobjects.count" in the immediate
window, and it showed "2"

The problem is, there is only one chart on that sheet. I tried another one
and it showed 6. I think there were probably old charts that were cut or
deleted that are still there somehow.

Is there a way to clear out the old charts, and if not, how can I identify
which chart is still on the sheet?




"Jon Peltier" wrote:

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

How do I modify charts thorugh VB
 

In Excel 2003 and maybe 2002, there's a button you can add to the Drawing
Toolbar. Click the arrow thingie on the right edge the Drawing toolbar,
click Add or Remove Buttons, Drawing, and choose Select Multiple Items.
Click on the new button that appears on the Drawing toolbar, and it will
give you a list of all the objects in the sheet.

When you delete columns or rows from behind a chart, the chart gets thinner,
becoming a one-pixel wide chart lined up along cell boundaries or along the
top or left edge of the sheet, where not even Superman could see it.

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


"Rayo K" wrote in message
...
OK,

I tried "msgbox sheets("17th-3").chartobjects.count" in the immediate
window, and it showed "2"

The problem is, there is only one chart on that sheet. I tried another one
and it showed 6. I think there were probably old charts that were cut or
deleted that are still there somehow.

Is there a way to clear out the old charts, and if not, how can I identify
which chart is still on the sheet?




"Jon Peltier" wrote:

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

How do I modify charts thorugh VB
 

To determine the name of the active chart, select it and click 'view' on the
toolbar and select 'Chart Window'.

"Peter T" wrote:

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