Vba create embedded chart on each worksheet problem

[Human_doing]

Hi all,

Can anyone please take a look at the below code, which is supposed to cycle through each worksheet on a workbook and add a chart based on range M1:N10? The macro doesn't actually seem to do anything, any help much appreciated,

Thanks

Sub Chart1()
Dim w As Worksheet
For Each w In ThisWorkbook.Worksheets
    Range("M1:N10").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=w.Range("M1:N10"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=w.Name
Next w
 
End Sub

 

[GlennUK]

How about putting w.Activate after the start of the loop?

 

[Misca]

You can't select things from sheets that are not active.

Try:

Dim WS As Worksheet
Dim Rng As Range
For Each WS In Worksheets
        Set Rng = WS.Range("N1:R10")
    With WS.ChartObjects.Add _
            (Left:=Rng.Left, Width:=Rng.Width, Top:=Rng.Top, Height:=Rng.Height)
        .Chart.SetSourceData Source:=WS.Range("M1:M10")
        .Chart.ChartType = xlColumnClustered
    End With
Next WS