Programmatically changing embedded chart series

I have copied a worksheet with an embedded chart and the series formula looks like this

=SERIES("Audits Planned (Cumulative)",'ABC Audit Status'!ChtLabel,'ABC Audit Status'!AuditsPlannedCumulative,1)

The problem is, the original worksheet name was 'ABC Audit Status' and I want it to reflect the named ranges on the current worksheet. (called BCD).   I have the following piece of code that I'm using (based on Jon Peltier's work)

Option Explicit

Sub ChangeSeriesFormula(myWS As Excel.Worksheet)

Dim myChtObj As Excel.ChartObject

Dim OldString As String

Dim NewString As String

Dim strTemp As String

Dim mySrs As Excel.Series

Dim myOldLoc As String

Dim myCht As Excel.Chart

Dim myVal As Long

Dim myWSName As String

Dim myChtType As Long

Dim myError As Long

Dim ChartChange As Boolean

For Each myChtObj In myWS.ChartObjects

   Set myCht = myChtObj.Chart

   For Each mySrs In myCht.SeriesCollection

      Debug.Print mySrs.Type

      Debug.Print mySrs.Name

      'Debug.Print mySrs.Formula

      strTemp = ""

      Debug.Print mySrs.ChartType

      strTemp = mySrs.Formula

      'strTemp = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)

      'mySrs.Formula = strTemp

      myVal = InStr(strTemp, myws.name)   

      If myVal = 0 Then

         'Need to update series

         myVal = InStr(strTemp, ",")

         myWSName = Right(strTemp, Len(strTemp) - myVal)

         myVal = InStr(myWSName, "!")

         myWSName = Left(myWSName, myVal - 1)

         strTemp = WorksheetFunction.Substitute(strTemp, myWSName, "'" & myWS.Name & "'")

         myChtType = mySrs.ChartType

         If myChtType <> xlColumnClustered Then

            mySrs.ChartType = xlColumnClustered

            ChartChange = True

         Else

            ChartChange = False

         End If

         If mySrs.Formula <> strTemp Then

            On Error Resume Next

            mySrs.Formula = strTemp   '<~~error occurs here   strTemp looks good and data exists  in series.

            myError = Err

            On Error GoTo 0

            If myError <> 0 Then

               MsgBox ("Error when changing series '" & mySrs.Name & "'.")

            End If

         End If

         If ChartChange Then

            mySrs.ChartType = myChtType

         End If

      End If

   Next mySrs

Next myChtObj

End Sub

Thanks in advance for your assistance,

Barb Reinhardt