Macro PivotTable question

[OleMiss2010]

I have a pivot table with a large amount of data. There are thousands of IDs that I need to filter down to about seventy. I have those IDs in the range("G2:G71") and I want the pivottable to only display those values.


I have my current macro below, but I get an error on the line where I tell it what to make visible.

[pre]

Sub Macro3()
For Each Cell In Range("G2:G71")
With Cell
Cell.Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ID")
.PivotItems(C1).Visible = True
End With
End With
Next Cell
End Sub

[/pre]

 

[SirJB7]

Hi, OleMiss2010!

I tried to reproduce your case but I get a previous error in the paste line. Can you upload the file?

Despite of that, wouldn't it be C1 quoted in the visible line?

Regards!

 

[Luke M]

I'm not sure why cell C1 was being used, but to just manipulate the PivotTable, I think it would be better to step through each item in the PT since we need to determine for each item whether to include it or not.

[pre]

Sub Macro3()
Dim InList As Boolean
Dim res As Variant
Dim MyRange As Range
Dim pItem

Set MyRange = Range("G2:G4")
Application.ScreenUpdating = False
'First, make sure everythings visible so we will never run into a
'error of hiding everything
For Each pItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("ID").PivotItems
pItem.Visible = True
Next
'Next, we step through our criteria
For Each pItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("ID").PivotItems
res = Application.Match(pItem.Name, MyRange, 0)
InList = (Not IsError(res))
pItem.Visible = InList
Next
Application.ScreenUpdating = True
End Sub

[/pre]