Multiple charts from one sheet

**AdilMK** · 08-30-2011, 04:53 PM

Hi,

I am not familiar with VB so I'm not sure how to write this macro, but I would like the script to plot each sample (See Sample.xls) on a different chart. I found a VB script on Microsoft's website that plots all samples on the same plot.

Sub CreateChart()
   ' Select the cell in the upper-left corner of the chart.
    ActiveSheet.Range("A1:T2").Select
   ' Select the current range of data. This line of code assumes that
   ' the current region of cells is contiguous - without empty rows
   ' or columns.
   Selection.CurrentRegion.Select
   ' Assign the address of the selected range of cells to a variable.
   myrange = Selection.Address
   ' Assign the name of the active sheet to a variable. This line is
   ' used in order to allow a chart to be created on a separate chart
   ' sheet.
   mysheetname = ActiveSheet.Name
   ' Add a chart to the active sheet.
   ' ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
   ' To create a chart on a separate chart sheet, remark out the
   ' previous line, and substitute the next line for the one above.
   Charts.Add
   Application.CutCopyMode = False
   ' This line can best be written by recording a macro, and
   ' modifying the code generated by the Microsoft Excel Macro
   ' recorder.
   ActiveChart.ChartWizard _
      Source:=Sheets(mysheetname).Range(myrange), _
      Gallery:=xlLine, Format:=4, PlotBy:=xlRows, _
      CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
      Title:="", CategoryTitle:="", _
      ValueTitle:="", ExtraTitle:=""
End Sub

I would modify this myself but I don't know the VB language.

Thanks.

**Andy Pope** · 08-31-2011, 04:25 AM

Something like this to output a chart for each row.

Sub CreateChart()

    Dim rngDate As Range
    Dim rngName As Range
    Dim rngData As Range
    Dim objCht As ChartObject
    Dim sngLeft As Single
    Dim sngTop As Single
    Dim sngWidth As Single
    Dim sngHeight As Single
    
    Set rngDate = Range("B1:J1")
    Set rngData = rngDate.Offset(1)
    Set rngName = Range("A2")
    
    sngLeft = 10
    sngTop = 100
    sngWidth = 250
    sngHeight = 200
    
    Do While Len(rngName) > 0
        Set objCht = ActiveSheet.ChartObjects.Add(sngLeft, sngTop, sngWidth, sngHeight)
        With objCht.Chart
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            .ChartType = xlLine
            With .SeriesCollection.NewSeries
                .Name = rngName
                .Values = rngData
                .XValues = rngDate
            End With
        End With
        Set rngData = rngData.Offset(1)
        Set rngName = rngName.Offset(1)
        sngTop = sngTop + sngHeight
    Loop

End Sub

**AdilMK** · 08-31-2011, 11:21 AM

Thanks a lot Andy, much appreciated. I have a small request tho. The Sample file I provided was just an example of what I needed. So, the actual amount of data might vary. I may have more dates or more samples. Basically I want the script to check the columns of dates until there is an empty column, same for the rows and then plot all the data. Is that possible?

Thanks.

**Andy Pope** · 08-31-2011, 11:24 AM

This change will do the date columns.

    Set rngDate = Range("B1",range("B1").end(xltoright))

Rows are automatically processed until a rngName , column A, is empty.

**AdilMK** · 08-31-2011, 11:49 AM

Works like a charm! Thank you very much. One last request, I would like to generate the charts in a separate sheet in the same excel file.

**Andy Pope** · 08-31-2011, 11:57 AM

just add the Location method to the loop

        With objCht.Chart
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            .ChartType = xlLine
            With .SeriesCollection.NewSeries
                .Name = rngName
                .Values = rngData
                .XValues = rngDate
            End With
            .Location xlLocationAsNewSheet
        End With