Getting Pivot Charts to Update

[Mikerose722]

I have a number of pivot charts in Excel that need to be updated monthly with new data that is added to the data source. The data source and all the Pivot Charts are in the same workbook but on a different tab. I thought I would be able to Refresh All to update the charts but that didn't work. I decided to right click on the chart to edit the data with the Select Data option. However, the Chart Data Range is grayed out. How can I get the chart to update with refreshing the data or at least be able to select the new data range?

 

[hartsie]

So you will replace the old data with new data monthly? You are simply trying to refresh all the pivot tables when new data is added to the source sheet, correct?

The refresh all should work with updating pivot charts.

Sub Refreshall ()

ThisWorkbook.RefreshAll

End Sub


I have a file where I do the same thing and the line of code that I run with VBA is basically the above.

That should work...

If it doesn't, you could try to clear the pivot cache.

I found this online that could help as well.

Sub PivotTableRefresh1()


Dim PvtTbl As PivotTable
Dim ws As Worksheet
Dim n As Integer
Dim i As Integer


For Each ws In ActiveWorkbook.Worksheets

n = 0

For Each PvtTbl In ws.PivotTables

n = n + 1

Next

If n >= 1 Then

For i = 1 To n

ws.PivotTables(i).PivotCache.Refresh

Next i

End If

Next

End Sub