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