Create charts with data from a different worksheet and varying number of columns

0

I need to generate a chart for each row in a database. This database varies both in number of columns and in number of rows, and is in a different spreadsheet, Groove Matrix 1. Whenever I run my code is giving an error in SetSourceData, and I can not identify what is happening.

Can anyone help me? Follow my code.

Sub createColumnChartMatriz12()

Dim ChartName As String
Dim Row As Integer
Dim ChartRow As Integer
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Matriz 1")
Dim k As Long
Dim z As Long

k = sh.Range("A1", 
sh.Range("A1").End(xlDown)).Rows.Coun
z = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column - 4
ThisWorkbook.Sheets("Matriz1Chart").Select
Cells.Select
Selection.RowHeight = 15.5
Cells(1, 1).Select


ChartRow = 49
Row = 2
For Row = 2 To k

ChartName = "Utilização no Período " & sh.Cells(Row, 1).Value

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select

With ActiveChart
    .SetSourceData Source:=sh.Range(Cells(Row, 4), Cells(Row, z)), _
            PlotBy:=xlRows
    .FullSeriesCollection(1).XValues = "='Matriz 1'!$D$1:$AM$1"
    .Parent.Height = Range("A1:A15").Height
    .Parent.Width = Range("A1:J1").Width
    .Parent.Top = Range("A" & ChartRow).Top
    .Parent.Left = Range("A" & ChartRow).Left
    .HasTitle = True
    .ChartTitle.Text = ChartName
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Meses"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Utilização"
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "mm-yyyy"
End With

ChartRow = ChartRow + 16

Next

End Sub
    
asked by anonymous 03.08.2018 / 03:25

0 answers