Embedding chart | SourceData

[Marissie]

Hi everyone! How are you?

I have a workbook where I work with a For Each loop to create a chart on each sheet but now I want to change the .CurrentRegion to collect data from column A and B but I can't make it work.

These are the lines of codes that I have now:

Sub CreateCharts()

Dim ws As Worksheet
Dim co As ChartObject

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Inhoudsopgave" Then
Set co = ws.ChartObjects.Add(300, 30, 600, 300)
With co.Chart
.ChartType = xlColumnClustered
.SetSourceData ws.range("A3:B3").End(xlDown))
.ChartColor = 11
.HasTitle = True
.ChartTitle.Caption = "Monthly Sales 2017"
.ChartGroups(1).VaryByCategories = True
.ChartArea.RoundedCorners = True
.ChartArea.Shadow = True
End With

'Move chart
co.Left = ws.range("F3").Left
co.Top = ws.range("F3").Top
co.Width = ws.range("F3:P3").Width
co.Height = ws.range("F3:P22").Height

Set co = Nothing

End If

Next ws

End Sub

And maybe you also know how to name the charttitle on each sheet as mentioned in cell B1?

 

[Marissie]

Do I have to dim the range or did I just do something wrong in my line to set the SourceData ?