#StackBounty: #excel #vba #pivot-table PivotChart – ActiveChart.Refresh works only when debugging

Bounty: 50

I have an Excel workbook in which I am generating a pivot chart per VBA from an existing pivot table. This chart is created as a new worksheet.

So far the result is as expected. But when I go to the generated chart and want to change something in the field editor I get an error message:

Pivot table report was saved without the underlying source data. Select “Refresh data”, to refresh the report. (Translated from German)

After doing so, I can work in the field editor. After a long nerve-wracking debugging session I figured out, that my code behaves differently when stepping through it with the debugger.

  • When executing the code normally, .Refresh has seemingly no effect.
  • When stepping through the code with the debugger, .Refresh works just as expected and I don’t get an error message in the field editor.

I tried it with Excel 2010 and 2013. Both versions show the same behavior.

  Charts.Add    
  chartSheetName = dqSource & "_PIVOT_CHART"
  With ActiveChart
    .Location where:=xlLocationAsNewSheet , Name:=chartSheetName 
    .HasTitle = True
    .ChartTitle.Select
    .ChartTitle.Text = "My chart title"  
    .Refresh ' <-- This is the suspect
  End With


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.