Why isn't this code working? To run a macro when Pivot Table changes

[Sophie.Durrant]

I got this code which I want to run each time my OLAP pivot table (PT) changes, so that the formulae in columns K-L will re-size to match the size of the PT. I created a named range called ReceiptAnalysis and I used ="OFFSET(ReceiptAnalysis!$A$5,0,0,COUNTA(ReceiptAnalysis!$A:$A),10)" to make the range change depending on the size of the PT. I'm wondering if it's because it's an OLAP PT instead of a normal one and if this is the case, is there a different method I can use to make it run when the PT is updated?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iSect As Range
    Set iSect = Application.Intersect(Target, Range("ReceiptAnalysis"))
    If Not iSect Is Nothing Then
        Call PivotMacro
    End If
End Sub
 
Sub PivotMacro()
    Dim ReceiptAnalysisSheet As Worksheet

    Set ReceiptAnalysisSheet = Worksheets("ReceiptAnalysis")
    
        With ReceiptAnalysisSheet
    
        Lrows = .Cells(.Rows.Count, "K").End(xlUp).Row
    
        .Range(.Cells(7, 11), .Cells(Lrows, "L")).ClearContents
    
        End With
    
        With ReceiptAnalysisSheet
    
        .Range("K6:L6").AutoFill Destination:=.Range("K6:L" & .Range("J" & .Rows.Count).End(xlUp).Row)
    
        End With
End Sub 

[buran]

Why using Worksheet_Change event and not use some of the worksheet events connected with PivotTable?

[Sophie.Durrant]

Sorry I'm not sure I understand what you mean. I am new to VBA so I found that first bit of code on a forum somewhere. The Sub PivotMacro bit is what I did and then I added it into the code I found

[buran]

Instead of Worksheet_Change event you can use code like this

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    If Target.Name = "YourPivotTableName" Then ' Change the name to match the name of your Pivot table
        ' some code here e.g.
        Call PivotMacro
    End If
End Sub

Here is reference to MSDN:
https://msdn.microsoft.com/en-us/lib.../ff822105.aspx

[Sophie.Durrant]

I tried it and it didn't work. I copied your code exactly, and I changed to the name of my PivotTable but it doesn't work!

[buran]

what does it mean that it didn't work? Where did you place the code (it should be in the sheet where the PivotTable is). When PT is updated does the code execute? To check this put a break point and update the table or add code to display msg (i.e. to be sure that PivotTableUpdate event is fired.

[buran]

Hi,
find attached sample with mock PivotTable and implementation of Worksheet_PivotTableUpdate event.
I've included 5 MsgBox to display the address of different parts of the PivotTable as my understanding is that you want to update the reference of a named range.
Change some values in the table on sheet1 and then refresh the PT on sheet2. Note that if you add rows, they will not be automatically reflected in the PT. For the example just change values in the existing table.
If you want you may want to explore also different events if someone will serve you better:

Worksheet_PivotTableAfterValueChange Event
Worksheet_PivotTableBeforeAllocateChanges Event
Worksheet_PivotTableBeforeCommitChanges Event
Worksheet_PivotTableBeforeDiscardChanges Event
Worksheet_PivotTableChangeSync Event
Worksheet_PivotTableUpdate Event -> the one which I implemented.

[Sophie.Durrant]

I think it must be because my PT is from an OLAP cube, I replictaed the data by copying/pasting values into another workbook and inserted a pivot table based on that. Copied your code and it worked.

I then modified your message boxes to my code and that worked, but when I copied the code over to my original report with the OLAP PT it gave me a weird error message:
Capture.PNG

[buran]

I just checked with my Excel 2007 VBA Programmer's Reference ant there IS an example with WorksheetPivoTableUpdate event when working with PivotTable from OLAP source. I just wanted to be sure that this event is applicable. So it should work... Obviously there is something we are missing here

[Sophie.Durrant]

Well if you don't know then there's absolutely no hope for me

[buran]

Hm, I have no idea what is that or what it could cause it. If someone can help... I don't have a clue

[buran]

Let's hope someone else could jump in and help.

[romperstomper]

Let's start from the beginning. If you use:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Msgbox "Running"
End Sub

and refresh your OLAP pivot, do you see the message "Running", or the error message?

[Sophie.Durrant]

I think IT were running some updates on the cube server which was causing the error message, so forget that part!

I did your code and refreshed the table, no message box appeared.

[Sophie.Durrant]

I think IT were running some updates on the cube server which was causing the error message, so forget that part!

I did your code and refreshed the table, no message box appeared.

[romperstomper]

And you definitely put it in the code module of the worksheet containing the pivot table? There shouldn't be any difference between OLAP and regular pivot tables in this respect - refreshing either should trigger this event.

[Sophie.Durrant]

I'm pretty sure I did. In the project explorer pane on the left i clicked on the sheet and then insert module, is that the right way to do it? There is only one sheet in the workbook anyway

Capture.PNG

[romperstomper]

No - just double click the sheet name (or from the main Excel Window, right click the sheet tab and then choose View Code. Your code currently is in a normal module, not the worksheet module.

[Sophie.Durrant]

It worked!!!! Thank you everyone for your help