Excel macro: method range of object _worksheet failed

Question

I've written a macro for an Excel template. The macro has to run on all sheets and change the source of each chart(each sheet has the same template and only one chart). When I run the following code it works fine but doesn't when there are many sheets(50 or more), at some point it gets the exception in the title. It didn't happen on the sheets before and it indeed changed the source.

Here's the code:

For Each ws In Sheets
    If ws.ChartObjects.Count > 0 Then
        If ws.Cells(1, "I").Value <> 1 Then
            ws.ChartObjects("Chart 1").Activate
            ws.Activate    
            ActiveChart.SetSourceData Source:=ws.Range("A17:B20")    
            ws.Cells(1, "I").Value = 1    
            ActiveCell.Select
        End If
    End If
Next

I've tried activating ws and using Activesheet I've also tried using With like this:

Dim rng as Range
With ws
    rng = .Range("A17:B20")
End With

No luck so far though.

Have you looked at the many other questions and answers here for this error message? stackoverflow.com/search?q=method+Range+of+object+_worksheet — nekomatic

John Coleman John Coleman · Accepted Answer · 2016-06-08T11:52:16

All of that selecting and activating is quite unnecessary. I'm not sure exactly why it would trigger that bug on occasion, though I think that it does.

Try:

For Each ws In Worksheets
    If ws.ChartObjects.Count > 0 Then
        If ws.Cells(1, "I").Value <> 1 Then
            ws.ChartObjects("Chart 1").Chart.SetSourceData Source:=ws.Range("A17:B20")
            ws.Cells(1, "I").Value = 1
        End If
    End If
Next

I also changed Sheets to Worksheets to guard against accidentally iterating over a chart sheet.

Excel macro: method range of object _worksheet failed

1 Answers