VBA Graph selection and Data range input

Question

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

Ah thank you, how would I edit it to include the range from column B to the final populated column? — fp2000
– fp2000, Commented Feb 7, 2023 at 16:39

Tim Williams · Accepted Answer · 2023-02-07 17:06:08Z

1

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

answered Feb 7, 2023 at 17:06

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Graph selection and Data range input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related