Update Pivot Chart upon use of slicer

[spydey]

So I have 3 worksheets that each have a number of pivot charts on them. The pivot data is on a different sheet than the pivot charts. Charts and Data are on separate sheets.

I have a number of slicers (4) on each sheet too .... all three sheets have the same slicers so that if I make a change to one slicer, it carries over to the other slicers & sheets.

I have data labels enabled on my pivot charts when the slicer filters are cleared out.

However, for some reason some of the times I select a slicer button, the data labels disappear. But if I choose a different button, they come back.

I don't want to have to go through all 46,640 different iterations just to find the ones where the data labels are not enabled (for some strange reason) to enable them.

So I figured a little VBA code would do the trick.

I have some code that enables data labels on all charts across all sheets, but it has to be manually run.

Here is the code:

Sub AddDataLabels_All() 
    Dim sr As Series
    Dim ws As Worksheet
    Dim chtObj As ChartObject
 
    For Each ws In Worksheets
        For Each chtObj In ws.ChartObjects
            For Each sr In chtObj.Chart.SeriesCollection
            
                sr.ApplyDataLabels
                
            Next sr
        Next chtObj
    Next ws
    
End Sub

I would prefer to have it auto-run whenever the slicer makes a change. To do that, I thought that maybe the following would work. Granted, I would have to place the code in each of the 3 worksheets, right, so that when the pivot for that worksheet was changed, it would be captured and then the code ran.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim sr As Series
    Dim ws As Worksheet
    Dim chtObj As ChartObject
 
    Set ws = ActiveSheet
        For Each chtObj In ws.ChartObjects
            For Each sr In chtObj.Chart.SeriesCollection
            
                sr.ApplyDataLabels
                
            Next sr
        Next chtObj
       
End Sub

But it didn't work as expected.

So I tested it by just changing it to:

Private Sub Worksheet_Active()

    Dim sr As Series
    Dim ws As Worksheet
    Dim chtObj As ChartObject
 
    Set ws = ActiveSheet
        For Each chtObj In ws.ChartObjects
            For Each sr In chtObj.Chart.SeriesCollection
            
                sr.ApplyDataLabels
                
            Next sr
        Next chtObj
       
End Sub

That worked!!! So that let's me know that the code works. But it means that if I make a change to the charts via the slicers, I then have to go to a different sheet and then back to the previous one for the code to work as it is only run once the worksheet is activated. It also means that for some reason, the Worksheet_PivotTableUpdate code is not being run correctly.

So I am trying to figure out where I went wrong with the Worksheet_PivotTableUpdate code (the 2nd set of code I posted above).

Could it be that because the actual pivot table data is on a different sheet than the chart, and I have the code in the chart's sheet rather than the data's sheet, that it isn't catching the changes made to the chart when I select the different slicers?

I am still fairly new to all this so just reaching out to get some guidance and direction.

What do you think?

-Spydey

 

[spydey]

Ok, so I just applied the Worksheet_PivotTableUpdate code to one of the sheets which contain the actual pivot table data, not the pivot chart. Going back to the chart sheet and using the slicers, the data labels appeared!!! YAY!!!! So that tells me that I need to have the code on the actual sheet that the pivot tables are located on .... which really is kind of a pain because I have approx. 12 different sheets with pivot tables on them!!! I don't want to have to go to each sheet and put the same code in each one just to get the data labels to appear.

Doesn't that seem somewhat lengthy and cumbersome? There has got to be a better way to do this .. I just wish I wasn't as blind to these solutions as I currently am .....

What do you think?

-Spydey

 

[SpillerBD]

iterate for each Worksheet?
Are your Pivot Tables running on the same PivotCache or did you force new caches for each?

 

[spydey]

iterate for each Worksheet?
Are your Pivot Tables running on the same PivotCache or did you force new caches for each?

I am not 100% sure on how to answer your question as I am not sure if they are or aren't. I am still wrapping my head around this.

I have a single set of data, in a table.

I generate a pivot table from that data set, onto a new worksheet.

I then go back to the data set and generate the next pivot table from the data set, onto a new worksheet. I don't' change the data set in any way.

I repeat this process until I have all the pivot tables I need, each on their own worksheet, but all coming from the same initial data set (in the form of a table).

I am not sure if this causes the pivot tables to all have the same PivotCache; or because I generate a new pivot table each time, even though it is from the same data source, it causes each pivot table to have a different (new) cache than the other pivot tables.

Maybe you could clarify that a bit for me, please?

-Spydey