Extracting Pivotable Selections

G

Guest

In a field, if you elect to only include certain items,
then when you select all of those items the table displays
(Multiple Items) rather than (All). Is there a way to
obtain a list of the items that are included (or excluded)
from the Multiple Items group? I know how to see the
list, but is there a way to move that info to cells in the
spreadsheet?
 
D

Debra Dalgleish

The following code will print the page field items that aren't hidden:

'============================
Sub ListFieldsVisible()
'lists visible page field items on a new worksheet
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim rw As Integer
Set ws = Worksheets.Add
ws.Activate
Set pt = Worksheets("Pivot").PivotTables(1)
rw = 0
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
On Error Resume Next
pf.CurrentPage = pi.Name
If pi.Visible = True Then
rw = rw + 1
ws.Cells(rw, 1).Value = pf.Name
ws.Cells(rw, 2).Value = pi.Name
End If
Next
Next pf
End Sub
'=========================
 
Top