VBA | For Each Loop

[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 let every chart 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.

 

[Momentman]

Untested though

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:B" & ws.Range("B" & Rows.Count).End(xlUp).Row)
                .ChartColor = 11
                .HasTitle = True
                .ChartTitle.Caption = ws.Range("B1").Value '"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

See if the above works for you

 

[Marissie]

Thank you very much for your time to help me. Unfortunatelly it doesn't work. The charttitle works perfectly though! When I run the code it still adds the rest of the columns.

 

[Momentman]

I forgot to change that line to

.SetSourceData Source:=ws.Range("A3:B" & ws.Range("B" & Rows.Count).End(xlUp).Row)

Does the above work?

 

[Marissie]

No, sorry. That doesn't work either.
I am new here so don't really know if it's possible to add a piece of my file to show you.