vba dynamically create multiple charts with each column of corresponding as a series

[sndameron]

I have almost 100 columns of data and I need to create a new scatter chart for each column of data with row 1 being the name of each series (header). Column A is the y-axis and will be the y-axis for each new chart. All columns from B on will be the x-axis. Is there a way to create a vba script that can generate a new chart for each column of data? I am at a complete loss. Here is an abbreviated table.

Age (y-axis)	Series1	Sereis2	Series3	Series4	Series5	Series6
45.37​	29.24​	4.70​	0.00​	0.00​	0.52​	0.00​
45.71​	37.79​	3.26​	0.00​	0.00​	1.30​	0.00​
45.78​	61.96​	0.00​	0.00​	0.48​	0.00​	0.00​
46.21​	76.72​	0.00​	0.00​	0.00​	0.00​	0.00​
46.56​	71.35​	0.00​	0.00​	0.00​	2.05​	0.00​
46.74​	61.67​	0.00​	0.00​	0.00​	0.00​	0.00​
46.78​	52.01​	0.00​	0.00​	1.44​	0.00​	0.00​
47.08​	52.21​	0.00​	0.00​	0.00​	0.00​	0.00​
47.29​	37.29​	0.00​	0.00​	0.00​	0.00​	0.00​
47.49​	61.52​	0.00​	0.00​	0.00​	0.00​	0.00​
47.60​	58.95​	0.00​	0.00​	0.00​	0.00​	0.15​
47.80​	36.66​	30.87​	0.00​	0.00​	0.00​	0.00​
48.31​	55.14​	0.00​	0.00​	0.00​	0.00​	0.00​
48.35​	47.99​	0.00​	0.00​	0.00​	0.00​	0.00​
48.35​	29.09​	0.00​	0.00​	0.00​	0.00​	0.00​
48.39​	35.28​	0.00​	0.00​	0.00​	0.00​	0.00​
48.43​	50.34​	0.00​	0.00​	0.00​	0.00​	0.17​
48.48​	49.06​	0.00​	0.00​	0.00​	0.00​	0.00​
48.50​	63.97​	0.00​	0.00​	0.00​	0.00​	0.00​
48.51​	8.36​	0.00​	0.00​	0.00​	0.00​	​
48.52​	18.06​	0.00​	0.00​	0.00​	0.00​	0.00​
48.53​	46.98​	0.00​	0.00​	0.00​	0.00​	0.00​
48.53​	71.03​	0.00​	0.00​	0.00​	0.00​	0.00​
48.54​	63.95​	0.17​	0.00​	0.00​	0.00​	0.00​
48.55​	59.22​	0.00​	0.00​	0.00​	0.00​	0.00​
48.56​	24.81​	0.00​	0.00​	0.00​	0.00​	0.00​
48.57​	56.16​	0.00​	0.00​	0.00​	0.00​	0.21​

<tbody> </tbody>

 

[jbeaucaire]

This sample workbook demonstrates popup charts of various types for any row you click in column A.


Charting Example 08.xlsm

 

[ParamRay]

.
.

Try the following macro, but make sure you select a cell outside your chart data range before running it:

Sub AddCharts()

    Dim i As Long
    Dim j As Integer
    
    i = Cells(Rows.Count, 1).End(xlUp).Row
    
    For j = 2 To 6
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatter
            .SeriesCollection.NewSeries
            With .SeriesCollection(1)
                .XValues = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(2, j), Cells(i, j)).Address
                .Name = "=" & ActiveSheet.Name & "!" & _
                    Cells(1, j).Address
                .Values = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(2, 1), Cells(i, 1)).Address
            End With
        End With
    Next j

End Sub

 

[sndameron]

That script did it. Thank you so much!!!! You have no idea how much time you have saved me. Now, I just need to figure out how to place the charts nicely side by side. I appreciate it. I know so little about vba. I can more or less read it and have a general idea of what the script is trying to accomplish but I don't know how to do it myself. Little by little I am picking it up so thank you!

.
.

Try the following macro, but make sure you select a cell outside your chart data range before running it:

Sub AddCharts()

    Dim i As Long
    Dim j As Integer
    
    i = Cells(Rows.Count, 1).End(xlUp).Row
    
    For j = 2 To 6
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatter
            .SeriesCollection.NewSeries
            With .SeriesCollection(1)
                .XValues = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(2, j), Cells(i, j)).Address
                .Name = "=" & ActiveSheet.Name & "!" & _
                    Cells(1, j).Address
                .Values = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(2, 1), Cells(i, 1)).Address
            End With
        End With
    Next j

End Sub

 

[sndameron]

Is there a way to get it to skip any columns that are blank. Also, what if I have #N/A in the cell? I was hoping it could just skip those cells and go to the next when it plots the series.


Thanks for your help!

.
.

Try the following macro, but make sure you select a cell outside your chart data range before running it:

Sub AddCharts()

    Dim i As Long
    Dim j As Integer
    
    i = Cells(Rows.Count, 1).End(xlUp).Row
    
    For j = 2 To 6
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatter
            .SeriesCollection.NewSeries
            With .SeriesCollection(1)
                .XValues = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(2, j), Cells(i, j)).Address
                .Name = "=" & ActiveSheet.Name & "!" & _
                    Cells(1, j).Address
                .Values = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(2, 1), Cells(i, 1)).Address
            End With
        End With
    Next j

End Sub