remove series from pivotchart - error 1004 (delete method of series class failed) - Excel 97

I have an embedded pivot chart that plots from a pivot table containing 2 data fields. The chart only plots one of the data fields - the other is programmatically removed (see code below).

Sub FormatPivotTables()

Dim co As ChartObject
Dim c As Chart
Dim sc As SeriesCollection
Dim s As Series
Dim mysheet As Worksheet
Dim wkbNewBook As Excel.Workbook

On Error GoTo FormatPivotTable_error

Set wkbNewBook = ActiveWorkbook

wkbNewBook.RefreshAll

With wkbNewBook
   For Each wksSheet In .Worksheets
       If InStr(wksSheet.Name, "#") Then
       
          Set mysheet = Sheets(wksSheet.Name)
          Set co = mysheet.ChartObjects(1)
          Set c = co.Chart
          Set sc = c.SeriesCollection

          For Each s In sc
              If LCase(InStr(s.Name, "% of total")) Or LCase(InStr(s.Name, "Total Number")) Or LCase(InStr(s.Name, "Grand Total")) Then
                 sc.Item(s.Name).Delete
              End If
          Next
       End If
   Next wksSheet
End With

Exit Sub

FormatPivotTable_error:
    ErrorOutput = sc.Item(s.Name).Name
    MsgBox "Procedure: FormatPivotTable" & Chr(13) & "Status: deleting " & sc.Item(s.Name).Name & Chr(13) & "Description: " & Err.Description, vbOKOnly, "Call Enquiry Analysis"
    Err.Clear

End Sub

The problem I have is whenever the pivot is updated and the program run, I get an error 1004 on the following line of code 'sc.Item(s.Name).Delete'.

If I manually remove the series and then run the code again it is fine.

Whatever I try I can't get it to work.