VBA Macro Code to Plot A “XYScatter Chart” on Fly
Sub chartData() On Error Goto ErrHandler Dim xRow As Long Dim wsName As String Dim ws As Worksheet Set ws = ActiveSheet wsName = ws.Name xRow = Cells(Rows.Count, "A").End(xlUp).Row ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmooth ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "='" & wsName & "'!$A$17" ActiveChart.SeriesCollection(1).XValues = "='" & wsName & "'!$A$20:$A$" & xRow ActiveChart.SeriesCollection(1).Values = "='" & wsName & "'!$B$20:$B$" & xRow With ActiveChart.Parent .Height = 325 ' resize .Width = 700 ' resize .Top = 200 ' reposition .Left = 400 ' reposition End With Exit Sub ErrHandler: MsgBox "The Error Number is - " & Err.Number & ": " & Err.Description MsgBox "Unable to continue" End Sub
This will create a scatter graph and will adjust for how many row there are as long as the data starts at row 20. To add this as a macro hit Alt F11 and this will open the VBA editor. On the Insert menu select Module. Paste the code into the module and then close the VBA editor. To run the macro just select Macros and run the macro called createGraph. It will create the scatter graph and you can resize to your liking by changing the areas I have marked as height/width/position
Add Multiple Series
With ActiveChart strName = "AddChartSeries" With .SeriesCollection.NewSeries .XValues = Sheets(strName).Range("E3:E" & arrLength(a)) .Values = Sheets(strName).Range("G3:G" & arrLength(a)) .Name = strName End With End With
If you need any more help just ask
Courtesy: Smuzoen