Run time error - PivotCache Failed

[Patrick1063]

Hi there,

Need assistance with this run time error



Just by clicking on any of the slicers, this run time appears. Please need help to solve this.

The following line in highlighted in the debug:

Worksheets("Priority Areas").PivotTables("PivotTable5").PivotCache.Refresh

Thanks in advance.

Regards,

Patrick

 

[NARAYANK991]

Hi Patrick ,

1. Change your code as follows :

Private Sub Worksheet_Change(ByVal Target As Range)
            Application.EnableEvents = False
            Worksheets("Priority Areas").PivotTables("PivotTable5").PivotCache.Refresh
            Application.EnableEvents = True
End Sub

2. Change the source data for your pivot table , to reflect the name of the workbook you uploaded ; it is excel file.xlsm where it should be :

excel file A.xlsm

3. More important , can you explain why this procedure needs to be used ?

Narayan

 

[Patrick1063]

Thanks for your reply Narayan.

Sorry I didn't understand number 2. above - "change the source data for your pivot table........" Can you please explain, thanks.

Number 3):

This worksheet has a pivot table that updates based on the selections on the slicers. The refresh button helps to reset all the slicers so that new selections can be made.

Thank you for your assistance.

 

[NARAYANK991]

Hi Patrick ,

There is only one procedure in the file you uploaded ; it is an event procedure named Worksheet_Change ; this runs when any cell in the worksheet is changed , and hence when ever you make a selection using any slicer , this procedure will run.

Now , when the procedure runs , it is refreshing the pivot table , which means the same event procedure is triggered once more ; such infinite loops should always be avoided. The change I have posted , ensures this will not happen.

However , that is not the main issue ; normally , a pivotcache refresh is required only when there are multiple pivot tables which are controlled by one pivotcache , so that refreshing the pivotcache refreshes all the pivot tables. As far as I can see , your workbook contains just one pivot table. I don't see the necessity for a pivotcache refresh.

When you make a selection using any one slicer , all the other slicers are automatically refreshed to reflect the correct selections that are available to them.

Secondly , the button labelled Refresh is assigned a macro named :

'excel file.xlsm'!Button1_Click

The file you have uploaded is named :

Excel file A.xlsm

The workbook you have uploaded does not contain a macro named :

Button1_Click

Something needs to be done about all of this.

Narayan

 

[Patrick1063]

Great thanks Narayan. That was very helpful. Excellent advise.

It works all good now.

Have a great day