VBA Help

  • Thread starter Thread starter ChrisK
  • Start date Start date
C

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
 
Greg - it works great - many thanks!


Greg said:
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
Click to expand...
 
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.
 
Back
Top