Run time error in VBA

[Pinkoto]

This code gives me an error when i open another excel file. It works great for what its supposed to do in the file it is located, but if i open another exel file (Which doesnt have any VBA) it gives me a run time error "The item with the specified name wasnt found". When i give debug it shows me this part of the code in yellow.

" Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart"

Any suggestions?

Private Sub Worksheet_Calculate()
    Dim cht As Chart, srs As Series
    Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    With Me
        cht.SetSourceData Source:=Range(.Cells(31, 2), .Cells(31 + .Range("K2").Value, 2 + .Range("L2").Value))
    End With
    For Each srs In cht.FullSeriesCollection
        With srs.Format.Line
            .ForeColor.ObjectThemeColor = msoThemeColorText1
        End With
    Next
End Sub

 

[jkpieterse]

Replace:

    Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart

with

    Set cht = ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 1").Chart

You may also need to figure out what worksheet the Range in SetSourceData should point to as it currently has no worksheet specified. Perhaps as simple as:

cht.SetSourceData Source:=.Range(.Cells(31, 2), .Cells(31 + .Range("K2").Value, 2 + .Range("L2").Value))