pivot table not getting updated

[Azhar]

Hi,

i have a workbook with two sheets. in one sheet, i have a dashboard with a
filter. in other sheet,i have a pivot table. the data is comming from a sql
cube.

suppose there is a field called geography, if i apply filter in the
dashboard page by selecting certain area in geography then the same should be
selected in the pivot table. i was able to gaet this requirement. but,

suppose for the first time i selected xyz as area then in pivot table also
its showing xyz. but if i select abc as area for next time, its still
displaying xyz in pivot table. intrestingly if i click on the dropdown of
pivot table, abc is selected but its displaying xyz. any help regarding this
please...

thought of using current page but i have used select multiple items so i
cant use that property.

thanks in advance,

Best Regards,
Azhar

 

[Roger Govier]

Hi

Try pasting this onto the Worksheet page containing the PT

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub

 

[Azhar]

hi,
thanks Roger..

i tried a workaround and it rally helped me..though to share it so that it
would be helpful...

actually the geography has hierarchies and is comming from a OLAP Cube... i
have also taken multiple selection in to consideration...what i came to knoe
is in this case, the pivot table items get stored in an array. If you want to
acess any of the elements then you need to pass it using array...



for example, my hierarchy is AREA-->REGION -->SUBREGION
at Area level I have 4 areas and i want to clear two of them then i have to
give it as..

Sheets("ActualBySummaryPricingLevel").PivotTables("PivotTable1").PivotFields("[Geography].[Geography].[Bigareaname]").VisibleItemsList
= Array("[Geography].[Geography].[areaname].&[abc]",
"[Geography].[Geography].[areaname].&[xyz]")

hope my understanding is correct,correct me if i am wrong...

Best Regards,
Azhar