Automatically create a set of charts from a block of data

[PierreBenko]

Hello!

I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.


One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.


Name
1 2 3 4 ...
A
B
C


D
E
F


For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.


Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro?
Or is there any easier method?

 

[b.downey]

Could you provide a sample of the data as well as the chart that is produced by the data

 

[PierreBenko]

Could you provide a sample of the data as well as the chart that is produced by the data

Here it is:
https://docs.google.com/open?id=0B2mnf9a30Gi8VG9wYVhpbUNDY2M

I have a lot from the block on the left side, and I want each block to have a set of graphs (like the ones one the right side).

 

[b.downey]

These Macros create all the Charts based on the data as povided in your example

Option Explicit

Sub CreateChart()
    Dim RowNo As Integer
    Dim Ws As Worksheet
    Dim strRng As String
    
    Dim RowBase As Integer
    Dim ColOffset As Integer
    
    Set Ws = ThisWorkbook.Worksheets(1)
    Ws.Range("A1").Select
    
    Dim ChtObj As ChartObject
    For Each ChtObj In Ws.ChartObjects
        ChtObj.Delete
    Next
    
    RowBase = 4
    For RowNo = 5 To Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row
        If Len(Trim(Ws.Cells(RowNo, "B"))) = 0 Then
            RowBase = RowNo
            ColOffset = 0
        Else
            strRng = "$B$" & RowNo & ":$G$" & RowNo
            
            Set ChtObj = Ws.ChartObjects.Add(100, 30, 400, 250)
            ChtObj.Chart.ChartWizard Source:=Ws.Range(strRng), _
                gallery:=xlLine, _
                Title:=Range("$A$" & RowNo), _
                HasLegend:=False
                
            Call CoverRng(Ws, ChtObj, RowBase, ColOffset)
            ColOffset = ColOffset + 1
        End If
    Next RowNo
    MsgBox "Complete"
End Sub

Function CoverRng(Ws As Worksheet, ChtObj As ChartObject, ByVal RowBase, ByVal ColOffset As Integer)
    Dim RngToCover As Range
    
    Const H As Integer = 7
    Const W As Integer = 5
    
    ColOffset = ColOffset * H + 8
    Set RngToCover = Ws.Range(Cells(RowBase, ColOffset), Cells(RowBase + H, ColOffset + W))
    ChtObj.Height = RngToCover.Height
    ChtObj.Width = RngToCover.Width
    ChtObj.Top = RngToCover.Top
    ChtObj.Left = RngToCover.Left
    
    
End Function

 

[PierreBenko]

Perfect. Thank you very, very much. I didn't got to learn VBA yet, but I think I should.
Thank you again.

 

[b.downey]

Glad I could help. Thanks for the feedback.