Hello all,

I'm hoping for some help to speed up a macro for a pivot table.

I have a pivot table with a pivot field that contains about 100 pivot
items. I've written some VBA code that will decide if the pivot item
should be visible or not.

I could do this manually by place the pivot filed in the rows, uncheck
the "show all" box and re-check for the fields I want visible
(right now only about 5 of the just over 100 pivot items). This would
cause Excel to recalculate once and show only the pivot items I want
visible.
With my VBA code Excel recalculate the pivot table for each pivot item
that is either hidden or shown. I've tried to enclose everything in a
"With pivotfield - end with". I've also tried to change the
calculation to manual with Application.Calculation =
xlCalculationManual before running this sequence (and changing it back
to automatic afterwards). From earlier I've also experienced that
pivot tables re-calculate even if the calculation method is set to
manual.

Is there another way to speed up the code so that the pivot table is
not re-calculated for all 100 pivot items?

Some of the code I'm using (not including the parts not related to
this selection, error handlers etc.):

Dim PT As PivotTable
Dim PTF As PivotField
Dim PTI As PivotItems
Dim myPivotItem As PivotItem

'The variables are set as the pivot table, field and items I'm
working with

Application.Calculation = xlCalculationManual
With PTF

For Each myPivotItem In PTI

Select Case myPivotItem
Case "Item xxxxxxxx1"
myPivotItem.Visible = True
Debug.Print "Visible " & myPivotItem
Case " Item xxxxxxxx2"
myPivotItem.Visible = True
Debug.Print "Visible " & myPivotItem

'etc. etc. going through the different cases

Case Else
myPivotItem.Visible = False

End Select

Next

End With
Application.Calculation = xlCalculationAutomatic


I'd be happy for any help on speeding up this.

Ronny

Thank you for your reply, Lonnie M. This made me remember that I have
read that it is much better to work with arrays than objects.
I don't see how I can use your code directly. My pivot table already
exists so I just want to make changes to it, not create it.

I still thought an array could be helpful, but I'm lost at how to
apply the full array to the pivotfield at once, and not loop through
the array.

If I have:
PTF As PivotField
PTI As PivotItems
ArrayItem(t) As String

With PTF
For a = 0 To t
PTI(ArrayItem(a)).Visible = False
Next a
End With

I'm still doing one and one pivot item, and get a refresh of the
pivot table for all of them.

What I want to do is:
With PTF
PTI(ArrayItem()).Visible = False
End With

But I can't get any code to work in such a way.. :(

Anyone that can help me?

Ronny

Ronny, try your original method with

PT.ManualUpdate = True

"Ronny" wrote:

Thank you for your reply, Lonnie M. This made me remember that I have
read that it is much better to work with arrays than objects.
I don't see how I can use your code directly. My pivot table already
exists so I just want to make changes to it, not create it.

I still thought an array could be helpful, but I'm lost at how to
apply the full array to the pivotfield at once, and not loop through
the array.

If I have:
PTF As PivotField
PTI As PivotItems
ArrayItem(t) As String

With PTF
For a = 0 To t
PTI(ArrayItem(a)).Visible = False
Next a
End With

I'm still doing one and one pivot item, and get a refresh of the
pivot table for all of them.

What I want to do is:
With PTF
PTI(ArrayItem()).Visible = False
End With

But I can't get any code to work in such a way.. :(

Anyone that can help me?

Ronny