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

Display only selected fields in a PivotTable with VBA
 

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

Display only selected fields in a PivotTable with VBA
 

Tom Ogilvy wrote:

for each pvtItm in
ActiveSheet.PivotTables("PivotTable3").PivotField s("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