CODEWITHSUNDEEP
excelvbacharts
fp2000
fp2000

Reputation: 1

VBA Graph selection and Data range input

I'm trying to select a graph on a worksheet which already exists and edit the data range by assigning it based on values in another sheet where it knows to keep looking for data until it runs out of columns.

I can't get it work with it not recognising the object correctly and other online help hasn't worked.

Sub GraphData()

Dim chrt As Chart

Sheets("ICB Time Series").ChartObjects("Chart1a").Select

Set chrt = ActiveChart

        With Sheets("Charts Data")
            chrt.SetSourceData Source:=.Range("B22:B23").End(xlToRight).Column
        End With

End Sub

Upvotes: 0

Views: 66

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Try something like this:


Sub GraphData()

    Dim chrt As Chart, rng As Range

    'get a reference to the chart (no need to use ActiveChart)
    Set chrt = Sheets("ICB Time Series").ChartObjects("Chart1a").Chart

    With Sheets("Charts Data")
        Set rng = .Range("B22", .Cells(22, Columns.Count).End(xlToLeft).Offset(1).Address(False, False))
        Debug.Print "Setting range", rng.Address()
    End With
    chrt.SetSourceData Source:=rng

End Sub

Upvotes: 1

Related Questions