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>
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,210
Members
430,197
Latest member
edeibold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top