Running graph-creating macro through multiple rows

[Tshelky]

Hey,

I dont and cant use VBA nor any other programming language at all but need this thing for my work, so sorry in advance for my phrasing;

I created a macro simply through "record macro" button in excel. The pourpose of it is to create a simple graph out of one row of a table.

Now I need to somehow make this macro run through every single row of this table and create a graph out of each one of them (the same way that it does for that one specific row that I recorded it for). I found some youtube tutorials on running a macro through rows but none of them focused specifically on graphs.

I am really sorry for asking this, but could someone advice me on how to write a code specifically for graphs? I been looking for this for hours but nothing seems to work at all.

This is how the code looks like currently;

Sub Likes_Grafy()
'
' Likes_Grafy Makro

'
    Range("B5:J5").Select
    ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range("CZ_data!$B$5:$J$5")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="CZ_grafy"
    ActiveSheet.ChartObjects("Graf 11").Activate
    ActiveSheet.Shapes("Graf 11").IncrementLeft -201.75
    ActiveSheet.Shapes("Graf 11").IncrementTop -81
    ActiveSheet.ChartObjects("Graf 11").Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.FullSeriesCollection(1).XValues = "=CZ_data!$C$3:$J$3"
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 0.2
    Range("L12").Select
End Sub

 

[Norie]

Can you post some sample data?

 

[Tshelky]

Sure,

changed the data as I cant post the real thing on the internet, but basically this is how the table and chart look like. Is a print screen enough?

 

[Norie]

Try this.

Sub Likes_Grafy()
Dim wsCharts As Worksheet
Dim wsData As Worksheet
Dim cht As Chart
Dim shp As Shape
Dim rngDst As Range
Dim rngValues As Range
Dim rngXAxis As Range
Dim ser As Series

    Set wsCharts = Sheets("CZ_grafy")
    Set wsData = Sheets("CZ_data")

    Set rngDst = wsCharts.Range("A1")
    Set rngValues = wsData.Range("B5:J5")
    Set rngXAxis = wsData.Range("C3:J3")

    Do

        Set shp = wsData.Shapes.AddChart2(332, xlLineMarkers)

        With shp
            .Left = rngDst.Left
            .Top = rngDst.Top
        End With

        Set cht = shp.Chart

        With cht
            .SetSourceData Source:=rngValues
            .Location Where:=xlLocationAsObject, Name:=wsCharts.Name
            ActiveChart.FullSeriesCollection(1).XValues = rngXAxis.Address(External:=True)
            .Axes(xlValue).MaximumScale = 0.2
        End With



        Set rngValues = rngValues.Offset(1)
        Set rngDst = rngDst.Offset(, 8)

    Loop Until rngValues.Cells(1, 1).Value = ""

End Sub

 

[Tshelky]

You are a life saver. Thank you so much!