Hi
I've Googled my brains out trying to find an answer to this, so I'm giving up and posting a thread here to see if I can get an answer.
I am using VBA to (try) and traverse a pivot field in a pivot table, where I have a page field with a single filter value selected.
When I loop through the pivot items for the field, I get all of the different values present in the field itself, but I acutally just want the items returned to the pivot which exist for the page field value.
Here's what it looks like:
BUSINESS Tax <- Page filter value
LOCATION LOCATION #
Melbourne 13100
Sydney 12100
Adelaide 15195
Perth 16130
In the example, my Page Field contains my Business names (eg Tax, Audit, Consulting)
I have 10 different locations these businesses may be found at - but Tax is only done in the 4 locations shown in the LOCATION pivot field.
When I traverse the Location field, I get all 10 locations, but what I want is just the 4 (shown) resulting after applying the Tax filter.
Clear as mud?!?
Maybe its some combination of the Page Field & Pivot Field in the code, but I just catn't figure it out.
I've read an article by Jon Peltier which shows you how to select the DataRange of a pivot field, but I just want to pick up the field's values and not have to select the range. I'm presuming this is possible?
MANY thanks in advance.
I've Googled my brains out trying to find an answer to this, so I'm giving up and posting a thread here to see if I can get an answer.
I am using VBA to (try) and traverse a pivot field in a pivot table, where I have a page field with a single filter value selected.
When I loop through the pivot items for the field, I get all of the different values present in the field itself, but I acutally just want the items returned to the pivot which exist for the page field value.
Here's what it looks like:
BUSINESS Tax <- Page filter value
LOCATION LOCATION #
Melbourne 13100
Sydney 12100
Adelaide 15195
Perth 16130
In the example, my Page Field contains my Business names (eg Tax, Audit, Consulting)
I have 10 different locations these businesses may be found at - but Tax is only done in the 4 locations shown in the LOCATION pivot field.
When I traverse the Location field, I get all 10 locations, but what I want is just the 4 (shown) resulting after applying the Tax filter.
Clear as mud?!?
Maybe its some combination of the Page Field & Pivot Field in the code, but I just catn't figure it out.
I've read an article by Jon Peltier which shows you how to select the DataRange of a pivot field, but I just want to pick up the field's values and not have to select the range. I'm presuming this is possible?
MANY thanks in advance.
