Pivot Table Filter values


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
 
Ad

Advertisements

S

Steve8733

Thanks Herbert for the quick turn around. That is EXACTLY what I needed.
Thanks again.
 
S

Steve8733

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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top