Macro to populate dual y axis chart

  • Hi,

    I'm about ready to tear my hair out.
    Have a WS named Charts and in it is a dual y axis graph.
    It used to work fine but the test data has now been moved to another WS.
    I've tried recording macros by Rt clicking on graph, selecting source data, and redefining the x & y values for both series.
    The macro doesn't work, I keep getting:
    "Unable to set the XValues property of the Series class"

    Here is the code:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R9C11:R11C11"
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!R9C20:R11C20"
    ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R9C11:R11C11"
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!R9C12:R11C12"
    Windows(File).SmallScroll Down:=0
    ActiveWindow.Visible = False

    Thanks for your help in advance!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!