Limiting/Hiding PivotItems

Can you post a sample wb?

using pivotfilters should be a lot faster than looping through all the pivotitems

Maybe something like this

Dim PT As PivotTable
Dim PTItem As PivotItem
Set PT = ActiveSheet.PivotTables(1)
For Each PTItem In PT.PivotFields("Department")
    If PTItem.Name = "510" Or PTItem.Name = "511" Or PTItem.Name = "513" Or PTItem.Name = "514" _
       Or PTItem.Name = "516" Or PTItem.Name = "517" Then
            PTItem.Visible = True
    Else
            PTItem.Visible = False
    End If
Next PTItem

my bad-not enough coffee. the approach I was thinking of is for autofilters and I don't suppose your data source is olap?
how many items are you looping through? I'm on a cell so can't see your workbook.
you may be able to speed it up by testing if each item is visible and then only hide it if it is and it's not a department you want.

can you alter the query that pulls from the data source? what is the data source-a db or cube?

ok. have you tried
-turning off subtotals
-setting the field sort to manual
-testing visibility before setting it
-turning off calculation and events

this is about all I can think of to try if you can't alter the data source

Private Sub CommandButton2_Click()
    Dim PT               As PivotTable
    Dim pf               As PivotField
    Dim Pi               As PivotItem
    Dim lCalc            As XlCalculation
    Dim wsFTE            As Worksheet

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .DisplayStatusBar = False
    End With

    Set wsFTE = ThisWorkbook.Sheets("Year-to-Date Paid FTEs")

    With wsFTE
        .Range("B3:D200").ClearContents
        .Range("G3:I10").ClearContents
    End With

    Workbooks.Open _
            "\\OurServer\SHARE10098\Budget\SOBUDGET\12MFR\Expense_Dtl_Reports\PAID_FTES_BR1112.xlsx", ReadOnly:=True, UpdateLinks:=True

    Set PT = ActiveSheet.PivotTables(1)

    With PT
        .ManualUpdate = True
        .RowGrand = False
        .ColumnGrand = False

        With .PivotFields("Department")
            .AutoSort xlManual, .SourceName
            .Orientation = xlRowField
            .Position = 1
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            On Error Resume Next
            For Each Pi In .PivotItems
                If Pi.Name = "510" Or Pi.Name = "511" Or Pi.Name = "513" Or Pi.Name = "514" _
                   Or Pi.Name = "516" Or Pi.Name = "517" Then
                    If Not Pi.Visible Then Pi.Visible = True
                Else
                    If Pi.Visible Then Pi.Visible = False
                End If
            Next Pi
            On Error GoTo 0
            .AutoSort xlAscending, .SourceName
        End With

        With .PivotFields("PAY_END_DT")
            .Orientation = xlPageField
            .Position = 12
        End With

        .RepeatAllLabels xlRepeatLabels
        .ManualUpdate = False

        With .TableRange1
            .Offset(2, 0).Resize(.Rows.Count - 2, .Columns.Count).Copy wsFTE.Range("B3")
        End With

        .ManualUpdate = True

        On Error Resume Next
        With .PivotFields("Posn Func")
            .AutoSort xlManual, .SourceName

            For Each Pi In .PivotItems
                Select Case Pi.Name
                Case "0025", "0058", "0059", "0109", "0110"
                Case Else
                    If Pi.Visible Then Pi.Visible = False
                End Select
            Next Pi
            On Error GoTo 0
            .AutoSort xlAscending, .SourceName
        End With
        .ManualUpdate = False

        With .TableRange1
            .Offset(2, 0).Resize(.Rows.Count - 2, .Columns.Count).Copy wsFTE.Range("G3")
        End With
    End With
    Workbooks("PAID_FTES_BR1112.xlsx").Close SaveChanges:=False

    With Application
        .DisplayStatusBar = True
        .EnableEvents = True
        .Calculation = lCalc
        .ScreenUpdating = True
    End With
End Sub

1. turning off sorting on a pivot field is an old trick used when hiding/showing items. I'm not sure if it makes any difference in newer versions of excel but it used to be invaluable in preventing errors.
2. the first line stores the current calculation setting (since it might not be automatic) and then that setting is restored by the second line. it's better practise than simply resetting to automatic calculation

Followup-The selection for the Posn Func codes isn't happening. I'm walking through the code to find out why.
Update-had to change this

Case Else
                    If Pi.Visible Then Pi.Visible = False
                End Select

to this

Case Else
                    Pi.Visible = False
                End Select

---------- Post added at 07:41 AM ---------- Previous post was at 07:33 AM ----------

JosephP,

I crossed over your last post, so just read it. Thanks for explaining, especially the 1Calc piece. I've always just returned calculation to automatic; nice to know there's a way to restore it to whatever it was when I came in.

Thanks for all your help on this. I don't know that we achieved much of a speed difference, but the code is a lot tighter and more efficient now, so I'm pretty happy. Thanks for helping and teaching.

my pleasure. I still have this idea in my head that pivotfilters ought to accept an array of values-as the pivot field dropdown effectively does-without having to loop (in olap pivots you can create a visibleitemslist). maybe in office 16 ;-)