Slicer not refreshing with Pivot/timeline

[VeryForgetful]

Hi,

I have several PivotTables where I update the month via a timeline slider in my worksheet.

In my sourcedata I also have a additional column which shows the relevant day for each row of data, =DAY(A2) etc. I have a slicer in my book that is linked to each of these tables which shows the days for each month, 1 through to 31. The problem I am having is that the number of days for the relevant month isn't automatically greying out days that don't exist. So for example if the timeline is selecting Feb 2016 then the slicer should grey out 29/02 and 30/02 as those days don't exist for that month.

Basically what I need to do is get the slicer and timeline to link to each other like they do independently with the PivotTables.

If I right click the slicer and refresh manually, the values are correctly updated and greyed out.

Recording a macro of this gives me:

ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables(1).PivotCache.Refresh

Any suggestions please?

 

[VeryForgetful]

Anyone please?

 

[Worf]

See if this works. You will need to change the names to match your project.

' sheet module
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "Pivot1" Then _
ActiveWorkbook.SlicerCaches("Volume").PivotTables(1).PivotCache.Refresh
End Sub

 

[VeryForgetful]

When I add this to my worksheet code it seems to get stuck in a loop constantly refreshing the pivot cache.

The slicer has a connection to approximately 10 PivotTables within the workbook and only seems to correctly update when I right click it and refresh.

I'm not sure how I can define a target when the slicer updates multiple tables at the same time.

 

[VeryForgetful]

I'm still struggling with this if anyone can assist please.

I've attached a sample workbook highlighting the issue.

https://www.dropbox.com/s/mguq9e5t5kf0jen/Book1.xlsx?dl=0

 

[Worf]

Hi

I’m on Excel 2016 today. It crashed a few times while testing this, then magically started to behave well. I got the following result when clicking a timeline month:

- January: all slicer days are selected
- February: days 1-29 are selected
- March: all days are greyed out

Tell me if you can reproduce this behavior; it takes a few seconds to process the request but eventually refreshes the day slicer.
Note the index, it’s referring to the first pivot table.

' sheet module
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "PivotTable1" Then _
ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables(1).PivotCache.Refresh
End Sub

 

[VeryForgetful]

Hi,

This works on my test file which only has one pivot. It takes a while but turning off screen updating speeds it up.

On my main file that has multiple pivottables connected to the slicer and timeline I still get method 'refresh' of object 'pivotcache' failed. I guess the issue must be due to the amount of source data and it has to refresh for each table individually?

It works perfectly if I put the code into it's own module but I don't want to have to manually click this every time I change the timeline. I guess I need to find a way to call the macro when the table refreshes but somehow avoid this having to go into a worksheet module as it appears that's where the issue is?

Rather than tracking a pivottable change the only other way that may work is if there is some code that can track a timeline change event.

My working code:

Sub test()


ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables(1).PivotCache.Refresh


End Sub

 

[VeryForgetful]

Something else that works manually is to remove all connections to the slicer then reconnect them, see code below. This still gives me the same problem though when I put the code anywhere near a worksheet event, whether it be a change event or pivottableupdate.

Sub RefreshSlicerConnections()




Application.ScreenUpdating = False


Dim PT As PivotTable
Dim i As Long
    
    
    
    With ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables
        For i = .Count To 1 Step -1
            .RemovePivotTable (.Item(i))
        Next i
    End With
    


ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables.AddPivotTable Sheet6.PivotTables("PivotTable1")
ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables.AddPivotTable Sheet6.PivotTables("PivotTable3")
ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables.AddPivotTable Sheet9.PivotTables("PivotTable1")
ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables.AddPivotTable Sheet9.PivotTables("PivotTable4")
ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables.AddPivotTable Sheet9.PivotTables("PivotTable5")




Application.ScreenUpdating = True


End Sub

[/IMG]

 

[Worf]

Maybe this?

' this workbook module


Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
If Sh.Name = "Sheet2" And Target.Name = "PivotTable1" Then _
ActiveWorkbook.SlicerCaches("Slicer_Day").PivotTables("PivotTable1").PivotCache.Refresh
End Sub