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