Macro on filtering pivot table (pivot fields) = debug
 

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

Macro on filtering pivot table (pivot fields) = debug
 

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html

Macro on filtering pivot table (pivot fields) = debug
 

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

"Debra Dalgleish" wrote in message
...
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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html