Pivot + VBA question
 

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

Pivot + VBA question
 

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.

--
Regards,
Tom Ogilvy



"Gunner" wrote in message
...
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

Pivot + VBA question
 

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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy



"Gunner" wrote in message
...
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


.

Pivot + VBA question
 

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



--
Regards,
Tom Ogilvy


Gunner wrote in message
...
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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy



"Gunner" wrote in message
...
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


.