Running graph-creating macro through multiple rows

Tshelky

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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;

VBA Code:
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data?
 

Tshelky

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Attachments

  • excel graf.jpg
    excel graf.jpg
    70 KB · Views: 4
  • excel tabulka.jpg
    excel tabulka.jpg
    143.2 KB · Views: 3

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You are a life saver. Thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,909
Messages
5,544,994
Members
410,647
Latest member
LegenDSlayeR
Top