Filtering pivot table from array

[Shaun]

Hi All,

I am trying to work out how to generate a pivot table using only those accounts select and stored in two arrays (DataSet1Array() and DataSet2Array()).

The trouble I am having is using each account number in the array to determine whether an item should be visible or not. The best I have been able to achieve is to keep the first account in the array visible and hiding all the others.

I have an example file and have manually filtered the rows to show the desired outcome.

Any assistance would be greatly appreciated.

Cheers,

Shaun

 

[NARAYANK991]

Hi Shaun ,

Suppose you go through all the account numbers in the first array ; at this stage , you can hide all the accounts which are not present in the array , and make visible all the accounts that are present in the array.

When you come to second array , there is no question of hiding accounts ; just go through the array , and if an item is present in the array , make its entry in the pivot table visible.

Thus , after both the arrays have been processed , all account numbers present in the two arrays will be visible in the pivot table.

Narayan

 

[Shaun]

Hi Narayan,

Thank you, your advice helped immensely!

I played around with the code and came up with:

'Sets visible property to all PivotItems except the last to False,
'but stores I for property changes later.
With ActiveSheet.PivotTables("PVTData").PivotFields("Account" & Chr(10) & "Number")
    For I = 1 To .PivotItems.Count - 1
        .PivotItems(.PivotItems(I).Name).Visible = False
    Next I
End With

' Sets visible property for DataSet1Array to True
With ActiveSheet.PivotTables("PVTData").PivotFields("Account" & Chr(10) & "Number")
    For I1 = LBound(DataSet1Array()) To UBound(DataSet1Array())
        j = DataSet1Array()(I1)
        .PivotItems(.PivotItems(j).Name).Visible = True
    Next I1
End With

' Sets visible property for DataSet2Array to True  
With ActiveSheet.PivotTables("PVTData").PivotFields("Account" & Chr(10) & "Number")
    For I2 = LBound(DataSet2Array()) To UBound(DataSet2Array())
        k = DataSet2Array()(I2)
        .PivotItems(.PivotItems(k).Name).Visible = True
    Next I2
End With
 
'Sets visible property I (from earlier) to False
With ActiveSheet.PivotTables("PVTData").PivotFields("Account" & Chr(10) & "Number")
    .PivotItems(.PivotItems(I).Name).Visible = False
End With

Thank again Narayan.

Cheers

Shaun