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

sndameron

New Member
Joined
Sep 21, 2014
Messages
27
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>
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

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

Code:
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

New Member
Joined
Sep 21, 2014
Messages
27
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:

Code:
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

New Member
Joined
Sep 21, 2014
Messages
27
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top