Pivot Table recalculations

  • Thread starter Thread starter C0ppert0p
  • Start date Start date
C

C0ppert0p

Hi All,
I have a VB macro that builds a Pivot table after reading in a csv
file.
When I recorded the macro, I selected only certain values from multiple
pivot fields to display but the macro records it this way:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Type*")
.PivotItems("Printer Desk Jet").Visible = False
.PivotItems("Printer Document Center").Visible = False
.PivotItems("Printer Ink Jet").Visible = False

Yep, it chooses what I don't want to display. This PivotField has over
500 values of which I only want to display ten and I have five
PivotFields I want to select from.

When I replay the macro the PivotTable get recalculated for every one
of the statements :(

There has to be a better way of doing this. Any suggestions.

Thanks in advance.
 
Got it:
I found a great solution written by Debra Dalgleish:

You could use code similar to the following:
Sub HidePivotItems()
'hide all pivot items in all tables on sheet
'except specified item

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
For Each pt In ActiveSheet.PivotTables
Set pf = pt.PivotFields("Role")
pf.AutoSort xlManual, "Role"
For Each pi In pf.PivotItems
If pi.Value = "Painter" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next
pf.AutoSort xlAscending, "Role"
Next
End Sub
 
Back
Top