Excel 2010 Pivot Table Change Event Issue

[ramkisuni]

Hi,
I have three worksheets Sheet 1, Sheet 2 and Sheet 3 with three PivotTables

Sheet1 - Sheet1Pvt1
Sheet2 - Sheet2Pvt2
Sheet3 - Sheet3Pvt3

When I use In Sheet 2 the following lines of code

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Application.EnableEvents = False
Application.ScreenUpdating = False
MsgBox "The pvt table refreshed " & Target.Name
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

and even If I Refresh Sheet 3 pivot table, Sheet 2 Event is triggered and it prints "The pvt table refreshed Sheet2Pvt2";

Not able to figure out how to restrict the event code only to Sheet 2 and pvttbl Sheet2Pvt2.

Thank you in advance

ramkisuni

 

[Jerry Sullivan]

Hi ramkisuni,

That might be happening if the PivotTables are all sharing the same PivotCache.

One workaround might be to "unshare" one or more of the PivotTables.

This article explains how to unshare the PivotCache.
http://office.microsoft.com/en-us/e...e-between-pivottable-reports-HA010226675.aspx

 

[ramkisuni]

Hi JS411,
Thank you very much, I got the problem solved by giving a new Dynamic Named Range for the same Data Range and gave the new name as the Data Source to the Pivot Table in my sheet which required a different formatting from other sheets;

Now when I run the "Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)" code by refreshing this Pivot Table, only this Pivot Table is affected. Any update in other Pivot Tables does not change this event in my sheet under work.

Can you pls tell me how do I mark this thread as "Solved"
(btw, I need to understand the link you provided more since I could not understand the relation between the Pivot Cache and Data Source in Pivot Table Options TAB; are they same?)


Thank you and appreciate your help

 

[Jerry Sullivan]

This Forum doesn't use the convention of marking threads as "Solved".

I believe the spirit behind that is to allow and encourage further suggestions or discussion even after one solution has been found.

Regarding the relationship between Pivot Cache and Data Source, the Pivot Cache is an area of memory that stores data used by one or more PivotTables and PivotCharts.

When the Pivot Cache is created, it matches the Data Source; however you could modify or delete data in the Data Source and it would not have any effect on the Pivot Cache unless the Pivot Cache is refreshed.

I believe the solution you discovered effectively created separate Pivot Caches that just happen to use the same range for their Data Source.