Display only selected fields in a PivotTable with VBA

[S30 via OfficeKB.com]

Hi all,

I would like to display dates in a pivottable that match with dates on a list.


I am one step away, but not sure where I made a mistake


'count number of dates in a pivot talbe
intCountRows1 = ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").
PivotItems.Count

'count number of dates in a list
intCountRows2 = Application.WorksheetFunction.CountA(Sheet1.Range("G:G"))

'tick all dates to be displayed
For i = 1 To intCountRows1
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.PivotItems(i).Visible = True
End With
Next

'compare a list against the dates in a pivottable and display only those that
match.
a = 2

Do While a <= intCountRows2

For i = 1 To intCountRows1

strField = Sheet1.Cells(a, 7).Value

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")

If .PivotItems(i).Value = strField Then
.PivotItems(i).Visible = True
a = a + 1
Else
.PivotItems(i).Visible = False
End If

End With
Next

Loop

End Sub

Thanks very much for your help in advance

Best regards

Slav

 

[Guest]

for each pvtItm in
ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").PivotItems
pvtItm.Visible = False
for a = 1 to intCountRows2
strField = Sheet1.Cells(a, 7).Value
If pvtItm.Value = strField Then
pvtItm.Visible = True
exit for
Next
Next PvtItm

 

[S30 via OfficeKB.com]

for each pvtItm in
ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").PivotItems
pvtItm.Visible = False
for a = 1 to intCountRows2
strField = Sheet1.Cells(a, 7).Value
If pvtItm.Value = strField Then
pvtItm.Visible = True
exit for
Next
Next PvtItm

Hi all,

[quoted text clipped - 47 lines]

Thank you very much!!!