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