Hi All,
I have a pivot table with one of the pivotfields on which there is a filter. The filter never changes, however depending on the source data, some items might be visible in the table and some might not.
For example let's say that the pivot field is called "Fruit" and that there is a filter on this field to keep only items called "Banana" and "Mellon".
Let's say that this week in the data source there is no mellon but only bananas.
If I do:
For each pitem in PVTfruits.pivotfields("Fruit")
If pitem.visible = true then msgbox pitem
end if
next pitem
It will display a msgbox for both Mellon and Banana (because they are ticked in the filtered), but this is not the result I am trying to achieve, It should only show "Banana" because this week (once the pvt is refreshed) there is no mellon in the source data.
Does anyone have an idea of how I can do this?
Many Thanks for your help
Tweedi
I have a pivot table with one of the pivotfields on which there is a filter. The filter never changes, however depending on the source data, some items might be visible in the table and some might not.
For example let's say that the pivot field is called "Fruit" and that there is a filter on this field to keep only items called "Banana" and "Mellon".
Let's say that this week in the data source there is no mellon but only bananas.
If I do:
For each pitem in PVTfruits.pivotfields("Fruit")
If pitem.visible = true then msgbox pitem
end if
next pitem
It will display a msgbox for both Mellon and Banana (because they are ticked in the filtered), but this is not the result I am trying to achieve, It should only show "Banana" because this week (once the pvt is refreshed) there is no mellon in the source data.
Does anyone have an idea of how I can do this?
Many Thanks for your help
Tweedi