Pivot + VBA question

  • Thread starter Thread starter Gunner
  • Start date Start date
G

Gunner

hi!
I have this formula.(See underneath) Is it possible that
the pivotitem can be picked up by direct the value of cell?
Something like this:
..PivotItems = ShtData.Range("E" & 9).Value

My problem is that there are 1000 different values in
PivotFields "Customers". If it was only 12 entries, I
could just listed them.

Private Sub OptionButton1_Click()
' Months only
Application.ScreenUpdating = False
With ActiveSheet.PivotTables(1).PivotFields
("Customers")
.PivotItems("Jan").Visible = True


Does anyone know how to solve this?

Gunner
 
It is unclear what you are trying to do.

Dim pvtItm as PivotItem
for each pvtItm in PivotTables(1).PivotFields("Customers")
sStr = pvtItm.Value & vbnewline
Next
msgbox sStr

so you can get the values for each of the pivot items from the PivotItems
collection.
 
Hi again,
I`m sorry. What I meant, was I want the pivot table to
show only entries that match a certain customer #.
So if I have the customer # in cell E9, I want to list the
every item that match the customer#.
First, I thought I had to put the cell value into a
variable and call that one, but it didn`t work...you
understand?
Gunner
 
Can't you make your customer number field a pagefield and select the
customer number from the dropdown.


if you want to hide every pivot item except the one in cell E9

Sub Pivt1()
Dim pvtItm As PivotItem
For Each pvtItm In ActiveSheet.PivotTables(1) _
.PivotFields("Customer").PivotItems
If UCase(pvtItm.Value) = UCase(Range("E9")) Then
pvtItm.Visible = True
Else
pvtItm.Visible = False
End If
Next

End Sub
 
Back
Top