Refresh all pivot table and associated pivot charts

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Refresh all pivot table and associated pivot charts

Post by gailb »

I have five pivot tables with five pivot charts and would like to change all of the tables/charts with a timeline.

Code: Select all

Sub Macro1()
    Dim i As Long
    For i = 1 To 12
        Sheets("Dashboard").Range("J5").Value2 = i
        ActiveWorkbook.SlicerCaches("NativeTimeline_Date").TimelineState.SetFilterDateRange Sheets("Dashboard").Range("I5"), Sheets("Dashboard").Range("K5")
        Application.Wait (Now + TimeValue("0:00:01"))
        ThisWorkbook.RefreshAll
    Next i
End Sub
This code works great in updating the pivot tables, but it fails to update the pivot charts. I'm basically trying to get a timeline in the chart so the user can view the difference and the months grow.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Refresh all pivot table and associated pivot charts

Post by HansV »

That is strange - a pivot chart should be updated automatically (and instantly) when the associated pivot table is updated.
Could you attach a small sample workbook?
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Refresh all pivot table and associated pivot charts

Post by gailb »

How's this. It was a very large file so I trimmed it down and just left two of the pivot tables/charts.
Data - Copy.xlsm
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Refresh all pivot table and associated pivot charts

Post by HansV »

I'll take a look after dinner.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Refresh all pivot table and associated pivot charts

Post by HansV »

Does this work better?

Code: Select all

Dim i As Long

Sub Macro1()
    i = 1
    Call DoIt
End Sub

Private Sub DoIt()
    Sheets("Dashboard").Range("K8").Value2 = i
    ActiveWorkbook.SlicerCaches("NativeTimeline_Date").TimelineState.SetFilterDateRange Sheets("Dashboard").Range("K5"), Sheets("Dashboard").Range("K11")
    ThisWorkbook.RefreshAll
    i = i + 1
    If i <= 12 Then
        Application.OnTime Now + TimeValue("0:00:02"), "DoIt"
    End If
End Sub
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Refresh all pivot table and associated pivot charts

Post by gailb »

Yes, much better. Thanks again.