Charts in Excel 97 vs 2000 - don't want pivot!

[fuzzword]

I'm having a problem in which a report created in Excel 97, which creates a chart using VBA code and uses data from a pivot table for its source.

The thing is, when it's opened in Excel 2000 (which only a few of our users have), the Chart turns into a PivotChart, and automatically selects TOO MUCH data for its source (it seems to select ALL of the Pivot Table data instead of just the data specified.

here is the line that selcts the data source:

chtGraph.SetSourceData Source:=Sheets _
("Pivot Table&quot.Range( _
"A16,A18:A22,V16:X16,V18:X22&quot, _
PlotBy:=xlColumns

Instead, it seems to select the entire pivot table as source data, and makes the chart a pivot chart.

Any ideas on how to coreect this - we don't want this to be a pivot chart, but Excel 2000 seems to do this automatically.

Thanks!

 

[tsuji]

Hello fuzzword.

Suggest you should consult the mskb article:

http://support.microsoft.com/support/kb/articles/Q221/0/41.ASP

for workarounds. As you prefer not to use PivotChart, there is a 2nd workworkaround by change data range off PivotTable.

regards - tsuji