Display only selected fields in a PivotTable with VBA

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

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

--
Regards,
Tom Ogilvy


"S30 via OfficeKB.com" wrote:

> 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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200605/1
>

Tom Ogilvy wrote:
>
>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!!!

--
Message posted via http://www.officekb.com

Tom, even though this was posted 5 years ago, it's a fantastic help to my current project.
Mike