Macro on filtering pivot table (pivot fields) => debug

[markx]

Hey guys,

I would like to ask you why I receive "Debug" question on the following
macro?

Sub PrintPivTab()
Dim i As Integer
Dim j As Integer
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TeamMembers")
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
' => once it arrives until the end of filtering and printing of all
the Team
' Members, it blocks here!! (.PivotItems(i).Visible = True)
For j = 1 To .PivotItems.Count
If j <> i Then .PivotItems(j).Visible = False
Next j
Range("A4").Select
Selection.CurrentRegion.Select
Selection.PrintOut
MsgBox .PivotItems(i).Name & " is now printing"
Next i
End With
End Sub

Should I maybe add somewhere that if j = i Then .PivotItems(j).Visible =
True? Would this resolve the problem?
Thanks so much for your help!!

Mark

 

[Debra Dalgleish]

If the field is set for automatic sort, you might get that error.
Add code to set the sort to manual, e.g.:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("TeamMembers")
.AutoSort xlManual, .Name

 

[markx]

hi Debra,

Unfortunately, even if I insert the code you suggested (.AutoSort xlManual,
..Name), the problem remains...So it should be something else then?

It's really a pity, because other than this, the code is doing a wonderful
job!
Rgds,
Mark