how to chart multiple sets of data quickly

**xelhelp** · 12-21-2011, 02:48 PM

Hi,

I am using Excel 2007. I have attached a sample spreadsheet to help explain what I'm trying to do.
example.xlsx
I have multiple sets of data (I have listed only a few sets on the sample spreadsheet) and I would like to chart these sets of data individually quickly using using a scatter plot with smooth lines and markers. All sets of data share the same X-values C1:I1. The series names are listed in column A. Y-values are listed in columns C - I. Each set of data has 8 series (actually it is 7 but I didn't know how to enter a title for each plot which are in A2, A10, A18, A26 etc. so I added another series with those cells) The Y-values associated with each series are in column C - I of the same row. I have created a plot with the first set of data in the example sheet, but I have hundreds of sets that I need to do the same thing for. Is there a way to quickly do this without having to manually select each set of data and plot it?

Thanks

**Andy Pope** · 12-22-2011, 08:44 AM

This will create and populate the chart objects.
You may well need to format other elements within the chart.

Sub MakeCharts()
    
    Dim rngXData As Range
    Dim rngTitle As Range
    Dim rngYData As Range
    Dim rngName As Range
    Dim chtTemp As Chart
    Dim objSeries As Series
    Dim lngIndex As Long
    
    Set rngXData = Range("C1:I1")
    Set rngTitle = Range("A2")
    Set rngName = Range("A3")
    Set rngYData = Range("C3:I3")
    
    Do While Len(rngTitle) > 0
        Set chtTemp = ActiveSheet.Shapes.AddChart.Chart
        With chtTemp
            .ChartType = xlXYScatterSmooth
            ' remove default data
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            For lngIndex = 1 To 7
                With .SeriesCollection.NewSeries
                    .XValues = rngXData
                    .Values = rngYData
                    .Name = rngName
                End With
                Set rngYData = rngYData.Offset(1)
                Set rngName = rngName.Offset(1)
            Next
            .HasTitle = True
            .ChartTitle.Text = rngTitle.Value
            With .Parent
                .Left = rngYData.Left + rngYData.Width + 10
                .Top = rngTitle.Top
                .Height = rngYData.Top - rngTitle.Top
            End With
        End With
        Set rngTitle = rngTitle.Offset(8)
        Set rngYData = rngYData.Offset(1)
        Set rngName = rngName.Offset(2)
    Loop
End Sub