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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

markx - 27 May 2008 16:20 GMT

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

Reply to this Message

Debra Dalgleish - 27 May 2008 19:12 GMT

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

> Hey guys,
>
[quoted text clipped - 26 lines]
>
> Mark

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

markx - 28 May 2008 09:32 GMT

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

> If the field is set for automatic sort, you might get that error.
> Add code to set the sort to manual, e.g.:
[quoted text clipped - 33 lines]
>>
>> Mark

Reply to this Message