Excel 2007 : VBA XY scatter plot problems

Hi all,

I am new to VBA, and I'm using Excel 2007 to do some automated graphing for work. Anyhow, I am having heck of a time getting a macro I am working on to do what I want. Here's what's happening:

1. I record the macro - I select both columns in the attached sheet - Go to insert chart- select the XY scatter plot - and it gives me a linear graph with the XY axis in the right place (A is X axis, B is Y axis). However, when I run this macro, it totally screws up the axis variables, and my graph comes out incorrect - I can eve tell where it's getting mixed up.

I realize at this point the chart wizard would be appropriate, but I need automation to be incorporated within this sheet. I am writing this macro because I will need to append this graph with new curves as I bring more columns of data in, but I can't even get it to do a simple plot yet - Any ideas?

Attached is the document

Here is the code for reference:

Sub Macro4()

    Columns("A:B").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'500mW impedance'!$A:$B")
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

End Sub

Thanks!

Jordan

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

I change the code slightly so it should be easier to adapt for future use.

Sub Macro3()
'
' Macro3 Macro
'
    Dim rngXData As Range
    Dim rngYData As Range
'
    Set rngXData = Range("A2", Cells(Rows.Count, 1).End(xlUp))
    Set rngYData = rngXData.Offset(0, 1)
    
    With ActiveSheet.Shapes.AddChart.Chart
        Do While .SeriesCollection.Count > 0
            ' remove any series added automatically
            .SeriesCollection(1).Delete
        Loop
        With .SeriesCollection.NewSeries
            .Values = rngYData
            .XValues = rngXData
            .Name = "='" & ActiveSheet.Name & "'!" & rngYData.Offset(-1, 0).Cells(1, 1).Address
        End With
        .ChartType = xlXYScatterSmoothNoMarkers
    End With
    
End Sub