VBA to set an axis in "Select Data" RRS feed

  • Question

  • I have several scatter plot charts on worksheet 1, I have my input data for the charts on worksheet2. I am able to read and display the range values in cells. I would like to use these ranges in my "Select Data" of my chart to update it. The ranges change and each time I have to go to select data and input the range. Would like a VBA that will do this for me. Can anyone direct me to something or somewhere that might help with this.


    Wednesday, July 22, 2015 7:04 PM


All replies

  • Hi Frank,

    Try using the SeriesCollection...

    Activechart.SeriesCollection(1).Formula = "Series(MySheet!$A$2:$A$25)"

    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Thursday, July 23, 2015 10:52 PM
  • Hi Frank2day,

    To update the chart data, we could set the source data range for that chart by using Chart.SetSourceData method.

    For example:

    Dim ws As Excel.Worksheet
    Set ws = ActiveSheet
    Dim co As Excel.ChartObject
    Dim chart As Excel.chart
    Set co = ws.ChartObjects(1)
    Set chart = co.chart
    chart.SetSourceData Source:=Sheets("Sheet8").Range("A11:B15")



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 24, 2015 1:57 AM
  • Thanks guys,

        I figured it out. My source data changes each time and had a Sub to read the range. Tacked it on the bottom of that to update the Chart. But I got it. Just took some time getting there. I had 12 charts to update. Once I got one right the others were a breeze. But thanks anyway.


    Friday, July 24, 2015 7:43 PM