VBA - Excel 2010 - Different errors (2147467259 and 1004) when creating multiple embedded charts in one worksheet

[NielsS]

For a couple of days now I'm trying to deblock an issue that I have when creating a macro to create three embedded charts from three existing pivottables.
I seem to can't get around the issue, so I direct myself to this forum in the hope to get an answer to why my code constantly get stuck.

Underneath you can find my code.
Before that I run this macro, I already created several pivottables through a separate macro. That macro works without problems, but when I subsequently run the following macro (to create my pivotcharts) I get an error:

Sub WeeklyBEApplCharts1()
'Remove all existing chartsobjects in the worksheet
If Worksheets("Fixed").ChartObjects.Count > 0 Then Worksheets("Fixed").ChartObjects.Delete

'Make pivotchart 1 based on pivottable Rep vs Age
Dim oChObj1 As ChartObject
Set oChObj1 = Sheets("Fixed").ChartObjects.Add(100, 100, 100, 100)

With oChObj1.Chart
.ChartType = xlBarStacked
.SetSourceData Source:=Sheets("Fixed").PivotTables("RepVsAge").TableRange1
.ShowAllFieldButtons = False
.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
.SeriesCollection(2).Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Axes(xlCategory).ReversePlotOrder = True
.Axes(xlValue).MinimumScale = 0
End With

'Make pivotchart 2 based on pivottable Plan Vs Besl
Dim oChObj2 As ChartObject
Set oChObj2 = Sheets("Fixed").ChartObjects.Add(100, 100, 100, 100)

With oChObj2.Chart
.ChartType = xlBarStacked
.SetSourceData Source:=Sheets("Fixed").PivotTables("PlanVSBesl").TableRange1
.ShowAllFieldButtons = False
.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(146, 208, 80)
.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
.SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
.Axes(xlCategory).ReversePlotOrder = True
End With

'Make pivotchart 3 based on pivottable Term Vs Besl
Dim oChObj3 As ChartObject
Set oChObj3 = Sheets("Fixed").ChartObjects.Add(100, 100, 100, 100)

With oChObj3.Chart
.ChartType = xlBarStacked
.SetSourceData Source:=Sheets("Fixed").PivotTables("TermVSBesl").TableRange1 'TableRange1 neemt automatisch de range over van de benoemde pivottabel
.ShowAllFieldButtons = False
.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(146, 208, 80)
.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
.SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
.Axes(xlCategory).ReversePlotOrder = True
End With

End Sub

When I run this macro, I constantly get the following error which is located at the setting of the source data for my second pivotchart (so he always succeeds in creating the first chart, but fails at the second one):

Run-time error '-2147467259 (80004005)': Method 'SetSourceData' of object '_Chart' failed


I already found some posts hinting to the solution for this issue of deleting the existing SeriesCollections after creating the chart but before setting the sourcedata.

For this reason, I added for all three pivottables a piece of code deleting the existing Seriescollections.

Example of the additional code for the first pivotchart (and which I repeat also for the other two):
Dim oChObj1 As ChartObject
Set oChObj1 = Sheets("Fixed").ChartObjects.Add(100, 100, 100, 100)
With oChObj1.Chart
.ChartType = xlBarStacked
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
.SetSourceData Source:=Sheets("Fixed").PivotTables("RepVsAge").TableRange1 'TableRange1 neemt automatisch de range over van de benoemde pivottabel
.ShowAllFieldButtons = False
.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
.SeriesCollection(2).Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Axes(xlCategory).ReversePlotOrder = True
.Axes(xlValue).MinimumScale = 0
End With

When I do this, I don't get the preceding error anymore, but I get another one aiming at the .SeriesCollection(1).Delete line:

Run-time error '1004': Application-defined or object-defined error

Ironically when I go to the immediate window afterwards and count the number of existing SeriesCollections (through ?oChObj1.Chart.SeriesCollection.Count), I do get as a result that 2 seriescollections exist. S
o I don't understand why he errors and doesn't want to delete the seriescollections.

And because I got the error, I tried deleting this piece of code again, but this results of course in getting the previous error (on setsourcedata).
So it looks like I'm in a Catch 22 situation!


Any help is very much appreciated to help me out!

 

[mole999]

does worksheet fixed only contain charts ? and does it have any code in the back of sheet (it might be able to be run from a workbook module?)

if no to both I would delete the sheet then add it to makes sure its new


1004 indicates something is not found normally, so a sheet doesn't exists yet, or not looking at the right part of the workbook

you might try On Error Resume Next above that line if there is an initial failure until you find a better error trap

 

[NielsS]

Thanks you for the quick reply.

My workbook has in total 7 sheets before I start any macro.

During macro 1, I create 5 extra worksheets that include several pivottables. One of these created sheets is the above worksheet called 'Fixed'. In that sense, it is a 'new' sheet.
After launching macro 1 (that created my 'Fixed' sheets and populated it with three pivottables), I immediately hit macro 2 which is the one creating the embedded charts in worksheet 'Fixed' based on the existing pivottables (that were created as a consequence of macro 1) and during which I get the above stated errors (why put them in two different macro's? because I wanted to isolate the charts issue from the correct code creating the pivot tables).

I'm not sure what you mean by 'does it have any code in the back of the sheet'?

 

[mole999]

so its a new sheet the code won't reside there (deleting a sheet that has code would be an issue)

maybe i'm missing it, i'm only seeing 'fixed' as a sheet. possible thats why 1004 is occuring ?

 

[NielsS]

so its a new sheet the code won't reside there (deleting a sheet that has code would be an issue)

maybe i'm missing it, i'm only seeing 'fixed' as a sheet. possible thats why 1004 is occuring ?

The VBA code is not created at the sheet level but in a module of the workbook (so in the VBA screen it is under VBA Project -> Modules and not under VBA Project -> Microsoft Excel Objects -> Sheet 8(Fixed) as with code for a specific sheet).
Could this be the problem?
I normally always create my macro's under the module map and they tend to always work (such as the macro 1 creating the sheet and the pivot tables).

 

[NielsS]

To make things very clear, this is the part of my first macro that creates the Sheet (called 'fixed') and the three pivottables. This macro runs correctly.
It's these three pivottables created here that I use in the second macro for my pivotcharts and it's this macro where the issues start (with the errors stated above).
'Add Sheet 'Fixed'
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Fixed"

'Empty pivotcache
Dim pc As PivotCache
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

'Create Pivot 1: SalesRep vs Age
Dim pvt As PivotTable
Dim pvc As PivotCache
Dim pvt1 As PivotTable
Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("CognosReport").Range("A1:AU" & lRowCognos))
Set pvt = pvc.CreatePivotTable(TableDestination:=Worksheets("Fixed").Range("A2"), TableName:="RepVsAge")

pvt.PivotFields("N Bs U").Orientation = xlColumnField
pvt.PivotFields("SalesRep").Orientation = xlRowField
Sheets("Fixed").PivotTables("RepVsAge").CompactLayoutColumnHeader = "New/Used"
Sheets("Fixed").PivotTables("RepVsAge").CompactLayoutRowHeader = "SalesRep"
Set pvt1 = Sheets("Fixed").PivotTables("RepVsAge")
pvt.AddDataField pvt1.PivotFields("A#"), " ", xlCount
Dim lRowRepVsAgeC As Long
lRowRepVsAgeC = Sheets("Fixed").PivotTables("RepVsAge").TableRange1.Rows.Count + 5

'Create Pivot 2: Plan vs Decision
Set pvt = pvc.CreatePivotTable(TableDestination:=Worksheets("Fixed").Range("A" & lRowRepVsAgeC), TableName:="PlanVsBesl")

pvt.PivotFields("Decn").Orientation = xlColumnField
pvt.PivotFields("Planname").Orientation = xlRowField
Sheets("Fixed").PivotTables("PlanVsBesl").CompactLayoutColumnHeader = "Beslissing"
Sheets("Fixed").PivotTables("PlanVSBesl").CompactLayoutRowHeader = "Plannaam"
Set pvt2 = Sheets("Fixed").PivotTables("PlanVsBesl")
pvt.AddDataField pvt2.PivotFields("A#"), " ", xlCount
Dim lRowPlanVsBeslC As Long
lRowPlanVsBeslC = Sheets("Fixed").PivotTables("PlanVSBesl").TableRange1.Rows.Count + lRowRepVsAgeC + 3

'Create pivot 3: Term vs Decision
Set pvt = pvc.CreatePivotTable(TableDestination:=Worksheets("Fixed").Range("A" & lRowPlanVsBeslC), TableName:="TermVSBesl")
pvt.PivotFields("Decn").Orientation = xlColumnField
pvt.PivotFields("TM").Orientation = xlRowField
Sheets("Fixed").PivotTables("TermVSBesl").CompactLayoutColumnHeader = "Beslissing"
Sheets("Fixed").PivotTables("TermVSBesl").CompactLayoutRowHeader = "Looptijd"
Set pvt3 = Sheets("Fixed").PivotTables("TermVSBesl")
pvt.AddDataField pvt3.PivotFields("A#"), " ", xlCount
Dim lRowTermVSBeslC As Long
lRowTermVSBeslC = Sheets("Fixed").PivotTables("TermVSBesl").TableRange1.Rows.Count + lRowPlanVsBeslC + 3