VBA: Runtime Error - SetSourceData for Second Pivot Chart

Question

I am getting:

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

intermittently when I try to run the following script:

Sub CreatePiePivot()
'Define worksheets
Set PSheet = Sheets("Tools")
Set DSheet = Sheets("Aggregate")

'Define last data points
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Selects first to last filled row, and first to last filled column for data
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Create pivot cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange)

'Moves user to Dashboard
Sheets("Tools").Activate

'Create blank pivot table
Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Range("F1"), _
        TableName:="ExcPT1")

'Create blank pivot chart
PSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData _
        Source:=Range("$F$2:$H$19"), _
        PlotBy:=xlRows

ActiveWorkbook.ShowPivotTableFieldList = False

With ActiveChart.PivotLayout.PivotTable.PivotFields("Exception")
    .Orientation = xlRowField
    .Position = 1
End With

'Insert Data
With PSheet.PivotTables("ExcPT1").PivotFields("Exception")
    .Orientation = xlDataField
    .Position = 1
    .Caption = "Exception Status Count"
    .Function = xlCount
End With

'Hide Not Due
With ActiveChart.PivotLayout.PivotTable.PivotFields("Exception Status")
    .PivotItems("Not due").Visible = False
End With

'Move bar chart to Dashboard; resize
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:="Dashboard"

Set ChartWidth = Sheets("Dashboard").Range("B26:L49")
With ActiveChart.Parent
    .Height = ChartWidth.Height
    .Width = ChartWidth.Width
    .Top = ChartWidth.Top
    .Left = ChartWidth.Left
End With

With ActiveChart
    .HasTitle = False
End With

End Sub

This sub is running after another very similar sub that creates a pivot bar chart using a sub connected to a button:

Sub CreatePivots()

Call CreateBarPivot
Call CreatePiePivot

End Sub

The BarPivot runs perfectly, no problem, every time, and looks almost identical to the PiePivot shown above with the applicable changes to display the appropriate data. If I run those subs separately, it usually runs without a problem. I ran-and-deleted the sub and results three times before I got the error. The debug is pointing at this line:

    ActiveChart.SetSourceData _
        Source:=Range("$F$2:$H$19"), _
        PlotBy:=xlRows

...but it looks exactly like the one used by the BarPivot. Since it's not happening exactly every time, but most of the time, I'm not sure where to start on troubleshooting. The documentation I've looked up (which is sparse by the way, or I'm looking in the wrong places) indicates I'm doing this right.

What's causing the error, and how do I fix it?

Answer 1

This is an example to illustrate how to avoid using Select and Activate in your code, plus a few other (hopefully useful) comments.

Always Use Option Explicit - this can't be emphasized enough

This will turn the first few lines of your Sub into:

Option Explicit

Sub CreatePiePivot()
    Dim thisWB As Workbook
    Set thisWB = ThisWorkbook

    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Set PSheet = thisWB.Sheets("Tools")
    Set DSheet = thisWB.Sheets("Aggregate")

Notice that by defining thisWB, you're setting up a workbook reference in a single line that, if you ever needed to change workbooks, change that one line and you're done. Also, this reference can explain why you should use ThisWorkbook over ActiveWorkbook.

Because some of your issues involve assumed references (either workbooks or worksheets or charts), the next lines can help show some necessary references that are easy to miss:

    'Define last data points
    Dim lastRow As Long
    Dim lastCol As Long
    lastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
    lastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column

Notice here that you have to reference DSheet inside of the Cells reference to guarantee that you're referring to the same worksheet. Otherwise VBA could assume that you're referring to the ActiveSheet, which may be completely different and give you an incorrect value.

The next few lines become:

    'Selects first to last filled row, and first to last filled column for data
    Dim PRange As Range
    Set PRange = DSheet.Cells(1, 1).Resize(lastRow, lastCol)

    'Create pivot cache
    Dim PCache As PivotCache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
                 (SourceType:=xlDatabase, SourceData:=PRange)

Your next statement in your code is Sheets("Tools").Activate which, unless there is a specific reason you want to display that worksheet to the user at this time, is completely unnecessary. If you want the user to view this worksheet when all processing is completed, move this statement to the end of the Sub.

Next:

    'Create blank pivot table
    Dim PTable As PivotTable
    Set PTable = PCache.CreatePivotTable _
                 (TableDestination:=PSheet.Range("F1"), _
                  TableName:="ExcPT1")

The real fix for your error comes next:

    'Create blank pivot chart
    Dim PChart As Chart
    Set PChart = PSheet.Shapes.AddChart
    PChart.ChartType = xlPie
    PChart.SetSourceData Source:=PSheet.Range("$F$2:$H$19"), PlotBy:=xlRows

Because you're creating an object for the newly added chart, you never have to use ActiveChart. It follows the same idea of specifying complete references.

And so on... you can hopefully use these examples to finish out the rest of your code and work through some of the issues you're having. Give it a try.