Set source data issue...

[BigDelGooner]

hi all

I am trying to set the source data range for a chart within VBA because each time the query is run the range will change.

I am using the following code but when it gets to the .setsourcedata line I get an 'application-defined or object-defined' error.

With ActiveChart.Axes(xlValue)
.MinimumScale = Application.WorksheetFunction.Floor(MinScale - 1, 10)
.MaximumScale = Application.WorksheetFunction.Ceiling(MaxScale + 1, 10)
.SetSourceData Source:=Sheets("Report_Indexed").Range(Cells(2, 2), Cells(End_Row, End_Column)), PlotBy:=xlColumns
End With

Any ideas?

 

[VoG]

Try fully qualifying the range

.SetSourceData Source:=Sheets("Report_Indexed").Range(Sheets("Report_Indexed").Cells(2, 2), Sheets("Report_Indexed").Cells(End_Row, End_Column)), PlotBy:=xlColumns

 

[BigDelGooner]

Thanks VoG but now I get a 'Object doesnt support this property or method' error...

 

[VoG]

Try

With ActiveChart.Axes(xlValue)
    .MinimumScale = Application.WorksheetFunction.Floor(MinScale - 1, 10)
    .MaximumScale = Application.WorksheetFunction.Ceiling(MaxScale + 1, 10)
End With
ActiveChart.SetSourceData Source:=Sheets("Report_Indexed").Range(Sheets("Report_Indexed").Cells(2, 2), Sheets("Report_Indexed").Cells(End_Row, End_Column)), PlotBy:=xlColumns

 

[BigDelGooner]

Ah ha! Why is it always the most basic things I miss!

Thank you v much Vog!