VBA - show all pivotitems based on object array

[murdo900]

What I'm trying to do is to get excel to show selected or all pivotitems that I have loaded into an array - without looping through each array value, as this requires huge amounts of calculation. A way of thinking about what I want to do is the "with" statement ie

With ActiveSheet.PivotTables("PivotTable1").PivotFields("GOR")
.PivotItems("GOR1").Visible = True
.PivotItems("GOR10").Visible = False
.PivotItems("GOR11").Visible = True
.PivotItems("GOR2").Visible = False
End With

Except that I have heaps more entries than that.

Sub pivot_items_show_mrexcel()
Sub pvt_items_show()
Dim arr_pvt_items() As Variant
Dim pvt_ref As Variant
Dim counter As Integer
Set pvt_ref = Worksheets("Summary").PivotTables("PivotTable1").PivotFields("GOR")
ReDim arr_pvt_items(pvt_ref.PivotItems.Count)
For counter = 1 To pvt_ref.PivotItems.Count
Set arr_pvt_items(counter) = pvt_ref.PivotItems(counter)
Worksheets("data").Cells(2 + counter, 2).Value = arr_pvt_items(counter).Name
Next counter
 
'what I want to do is to make all items visible, without looping through each array value in order to avoid recalculating tables.
 
arr_pvt_items().Visible = True      'how do I get this line to work?
 
End Sub

 

[SydneyGeek]

You can set the table to refresh manually:

    For Each PT In ActiveSheet.PivotTables
        PT.ManualUpdate = True
    Next PT

This prevents automatic updates. Set it back to False when you want to refresh automatically.

Denis