Get visible pivot table items

[Carl Colijn]

Hi all,

I have a dedicated "data selection" pivot table on one of my sheets.
The user is free to move the fields into the rows, columns and report
filter. After the user has set up the fields the way he likes, and
after he has applied all filters he needs, I need to capture the total
filtering on the fields in order to apply it to the other pivot tables
in the same workbook. I want to do this by looking which items are /
aren't displayed in this "data selection" pivot.

And here comes the question: I already found out that I cannot simply
use PivotField.HiddenItems, since that won't work for page fields. I
therefore just loop through all fields and items and inspect their
PivotItem.Visible property, but now I find out this doesn't work for
label filters - it only returns if the item is manually selected
(ticked) / deselected in the field item list.

What options do I have? I know I can use PivotTable.ActiveFilters to
look what label filters have been applied, but I cannot seem to get how
they have been processed on the item list itself.

--
Kind regards,
Carl Colijn

TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
TimeTraces: the powerful and versatile time registration system!
http://timetraces.twologs.com

 

[minimaster]

I have written an add-in for myself that allows to save a pivottable
layout with all setttings and then I can restore this saved layout
when I want to.
Because there are problems to read certain filterings, as you
discovered,, I'm temporarly changing the field orientation, then
reading the filtering, and then restoring the field to its original
orientation.

I still have a one problem with the grouping of data, e.g. when dates
are grouped by months, quarters,.. I found no way that I can read the
current grouping settings. Looks like MS forgot to implement it. I can
set with VBA or VB.net the grouping but i didn't find a straightfwd
way to read the grouping settings.

 

[CellShocked]

I have written an add-in for myself that allows to save a pivottable
layout with all setttings and then I can restore this saved layout
when I want to.
Because there are problems to read certain filterings, as you
discovered,, I'm temporarly changing the field orientation, then
reading the filtering, and then restoring the field to its original
orientation.

I still have a one problem with the grouping of data, e.g. when dates
are grouped by months, quarters,.. I found no way that I can read the
current grouping settings. Looks like MS forgot to implement it. I can
set with VBA or VB.net the grouping but i didn't find a straightfwd
way to read the grouping settings.

right click on a header of the pivot table?

 

[minimaster]

  right click on a header of the pivot table?

...I'm talking about reading the grouping with VBA or VB.net code....

 

[Carl Colijn]

I have written an add-in for myself that allows to save a pivottable
layout with all setttings and then I can restore this saved layout
when I want to.
Because there are problems to read certain filterings, as you
discovered,, I'm temporarly changing the field orientation, then
reading the filtering, and then restoring the field to its original
orientation.

I still have a one problem with the grouping of data, e.g. when dates
are grouped by months, quarters,.. I found no way that I can read the
current grouping settings. Looks like MS forgot to implement it. I can
set with VBA or VB.net the grouping but i didn't find a straightfwd
way to read the grouping settings.

Hi minimaster,

The grouping settings do not bother me; it's just the data filtering
capabilities that the pivot is used for. However, my client has
decided to forego filtering the data via a pivot table alltogether, and
I'm going to build a VBA data selection userform instead.

Thanks for the answer though!

--
Kind regards,
Carl Colijn

TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
TimeTraces: the powerful and versatile time registration system!
http://timetraces.twologs.com