Pivot Table Filter values

  • Thread starter Thread starter Steve8733
  • Start date Start date
S

Steve8733

Is there a way to "read" what values you have selected for a Pivot Table
filter in Excel-07?
I have Report Filter(s) where I may select multiple values for any given
filter. Being that this is a Report Filter, the values are not displayed in
the data. For instance, say I have a Report Filter which contains 12 months
(Jan - Dec). If I select Jan, Feb & Mar in this filter, the pivot table then
reflects this data. However, short of clicking the drop down to see which
items I've selected, I have no way of knowing what months are being
displayed. I just want to be able to write these values to a cell/textbox
showing something along the lines of...."Selected months - Jan, Feb & Mar".

Thanks in advance
steve
 
Thanks Herbert for the quick turn around. That is EXACTLY what I needed.
Thanks again.
 
Upon further review, The solution given by Herbert did not accomplish what I
needed but no fault to him. I was not clear on my scenario.

I have my pivot table linked back to an Oracle datafeed meaning the raw data
is not readily available to scan thru. Besides it has over 250k records.

However I'm close to a soulution with one small problem to work thru. In the
code below, the line of code "If .PivotFields(FilterName).CurrentPage =
"(All)" Then" does not recognize I no longer have "(ALL)" selected and
therefore the IF statement remains true even after i've selected a specific
value.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
rw = 26
With PivotTables("PivotTable6")
For Each pvtField In .PageFields
rw = rw + 1
FilterName = pvtField.Name
Cells(rw, 10).Value = FilterName
If .PivotFields(FilterName).CurrentPage = "(All)" Then
Cells(rw, 11).Value = "ALL"
Else
Cells(rw, 11).Value = ""
n = .PivotFields(FilterName).PivotItems.Count
For i = 1 To n
If .PivotFields(FilterName).PivotItems(i).Visible Then
If Cells(rw, 11).Value = "" Then
Cells(rw, 11).Value =
..PivotFields(FilterName).PivotItems(i).Name
Else
Cells(rw, 11).Value = Cells(rw, 11).Value + ", "
+ .PivotFields(FilterName).PivotItems(i).Name
End If
End If
Next i
End If
Next pvtField
End With
End Sub
 
Back
Top