View Full Version : How to retrieve DataSource of chart

04-23-2009, 03:16 AM

I am quite new to VBA and EXCEL world.
I have following problem.

We plot a chart say chartA in sheetA of bookA, datasource of that chart is say RangeA in sheetA only


We move sheetA to bookB,


ChartA is reffering to RangeA in bookA only

My task is to write a macro (or create any other way) so that after moving chart to bookB, ranges should copy to new book as well.

So my idea is after moving a chart we can trigger a function which:

1)Retrive the NAME of data source of ChartA say:

oldRange = book1.xls!sheet1!RangeA

2) create a dupilcate range in new workbook that is bookB say

newRange = RangeA (this comes after extracting from oldRange)

3) Then change the data source to new range.

mychartobj.chart.setDataSource = RangeA

I am not able to find out data source of ChartA

is there is any way to know chart data source name?

like is there is any counter part of


which is:


Or any other approach for the problem will be very help ful thanks

06-07-2009, 09:36 AM
Moved to Excel forum