VBA Help

[ChrisK]

Hi all,

I'm trying to programatically change the source data of a chart using the
following code:

Dim Workbook As Workbook, Chart As Chart

Set Workbook = Excel.Application.ActiveWorkbook

Workbook.Sheets("NISTData").Activate

Workbook.ActiveSheet.Range("A1").Select

Workbook.ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

Set Chart = Workbook.Charts("NIST1976")

Chart.SetSourceData Workbook.ActiveSheet.Range(Selection,
Selection.Offset(0, 1)).Select

When I run this, I get a "Object Required" error on the last line
(essentially this is setting the source data of the chart to both the
columns in the "NIST1976" sheet)

What obvious thing have I missed?

thanks and kind regards

CK

 

[ChrisK]

Greg - it works great - many thanks!

Try:

Sub ChangeSourceData()
Dim SorRng As Range, Rw As Long
With Sheets("NISTData")
Rw = .Range("A1").End(xlDown).Row
Set SorRng = .Range("A1:B" & Rw)
End With
Charts("NIST1976").SetSourceData SorRng
End Sub

Regards,
Greg

 

[bruce taylor]

you don't have to use vBA to dynamically change source
data for graphs, can be done using named ranges. Found a
very helpful article (from California company I think).
Try a net search.