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:


    Windows(File).Activate
    Sheets("Charts").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    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
    Windows(File).Activate
    Range("L31").Select


    Thanks for your help in advance!
    Elaine

Participate now!

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