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

  • Thread starter Thread starter markx
  • Start date Start date
M

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
 
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
 
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
 
Back
Top