Thread: Creating Multiple Charts using VBA

Hi,

I am fairly new to VBA and need help with a report to create multiple charts. In the report you will see two tabs one marked Data the other charts. In the charts tab I have manually created two charts using some of the data from the Data tab. You can see it is a fairly simply pattern. Columns C and D are the titles for the series, and I need charts for every 4 rows. After every 4th row I would like to create a new chart. Row E6:BE6 (date) serves as the X value, while the data below are the values for the chart. I have only created two charts but you can see that I need to create charts for all the values. The problem is that the number of entries, will change. Here I have 8 set of 4's. I could have X number of sets of 4's. I need to write code to make sure I catch all the data. I have tried many different types of loops but can't seem to get it. It would be great if the charts could form right under each other, but that is not that important, as long as they don't form right on top of each other. Also exactly how the charts look is not that important either, something like what I have would be great. Chart title and series names are important...i.e. they need to keep changing as the data changes. If anyone needs more info or has questions please just let me know. Please any help anyone can give is greatly appreciated.


Tom


Sorry wasn't sure of the rules here is a link to another board I posted the question. I just really need help on this one.
http://www.ozgrid.com/forum/showthread.php?t=149308


Thanks,

Tom

Also posted here.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

Hi,

I had a quick play around with this. I'm a complete amateur, so the code is probably not as good as it could be, but hopefully it is a starter for 10.

Let me know if the attached doesn't work for you

(code used linked to the button on your data tab)

Sub Refresh()

'Variables
'wsCharts and wsData are the two worksheets
' conSpacing is the constant for the spacing between graphs (20 rows)
' this is based on how excel creates graphs on my machine, it changes if
' excel isn't full screen (I think)
' i and j are just counters
' LastRow is the final data entry in the table in column C

    Dim wsCharts As Worksheet
    Set wsCharts = Worksheets("Charts")
    Dim wsData As Worksheet
    Set wsData = Worksheets("Data")
    Const conSpacing = 20, conStartInRow = 6
    Dim i, j, LastRow As Long
    
'Delete all existing charts on the page

    wsCharts.ChartObjects.Delete

'Find the last data entry in column C

    With wsData
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    
'Add a blank chart
'Linemarkers is the chart type
'set a dummy data range, and tell the graph to plot by rows
'Put the chard in the charts worksheet
'Tell the sheet it has a title
'Set the x and y axis as Date and Hours
'Cut the sample chart

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
        ActiveChart.SetSourceData Source:=wsData.Range("E6:BE10"), PlotBy:= _
        xlRows
    ActiveChart.Location where:=xlLocationAsObject, Name:=wsCharts.Name
    With ActiveChart
        .HasTitle = True
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours"
    End With
    wsCharts.ChartObjects.Cut
    
'For 1 to the number of groups of four

    For i = 1 To (LastRow - conStartInRow) / 4
    
'If it is the first i, select cell A1, else select the cell according to the spacing
        
        If i = 1 Then
            wsCharts.Cells(1, 1).Select
        Else
            wsCharts.Cells((i - 1) * conSpacing, 1).Select
        End If
        
'Paste the chart to this location

        wsCharts.Paste
        
'Set the series 1 to 4, according to the 4 data ranges in the table
        
        For j = 0 To 3
    
            ActiveChart.SeriesCollection(j + 1).XValues = "=" & wsData.Name & "!R6C5:R6C57"
            
            ActiveChart.SeriesCollection(j + 1).Values = "=" & wsData.Name & "!R" & _
                                (i * 4) + (conStartInRow / 2) + j & "C5:" _
                                & "R" & (i * 4) + (conStartInRow / 2) + j & "C57"
        
            ActiveChart.SeriesCollection(j + 1).Name = "=" & wsData.Name & "!R" & _
                                    (i * 4) + (conStartInRow / 2) + j & "C3:" _
                                    & "R" & (i * 4) + (conStartInRow / 2) + j & "C4"
            
        Next

'Give the chart a title of the first label of the data series

        With ActiveChart
            .ChartTitle.Characters.Text = wsData.Cells(i * 4 + conStartInRow, 3)
        End With
    Next
    
    

End Sub