remove series from pivotchart - error 1004 (delete method of series class failed) - Excel 97
Posted on 2003-03-04
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).
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
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
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"
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.