Pivot Table

[spoonjy]

1. i created one PivotTable, in the RowAxis, i need the list to collapse.
how to do that in VBA?

2. For that particular RowAxis, I have 100 members.
eg.
Client Name
A1
A2
A3
.
.
.

how ever, i only want to include certain clients. I know the standard code is
.FieldSets("client name").Fields(0).IncludedMembers = Array("A1", "A2")

but, i need to pass in the list of names from another table.
so, for example, i have another recordset with the client names,
A2,
A3,
A5...
I need to pass each of these to be included in my RowAxis.
how to do that?

[DKenny]

Loop through each of the items in the pivotfield and compare it to the list of customers for inclusion. If its in the list then show it, oterwise hide it. Here's some sample code, you will need to change the reference to the correct pivotfield and to your customer list.

Sub FilterPivot()
Dim PvtFld As PivotField
Dim PvtItem As PivotItem
Dim CustomerRange As Range
Dim ChkCount As Integer
 
    
    Set PvtFld = Worksheets("PTable").PivotTables(1).PivotFields("client name")
    Set CustomerRange = Worksheets("PTable").Range("f1:f5")
    
    For Each PvtItem In PvtFld.PivotItems
        ChkCount = Application.WorksheetFunction.CountIf(CustomerRange, PvtItem.Name)
        If ChkCount = 0 Then
            PvtItem.Visible = False
        Else
            PvtItem.Visible = True
        End If
    Next PvtItem
 
Set PvtItem = Nothing
Set PvtFld = Nothing
End Sub