Selecting criteria in Pivot table

**jomili** · 05-11-2011, 11:09 AM

I need to know how to make a one size fits all selection for my pivot tables. The overall situation is; I have 12 regions, each of which has an Expense Report. I want to open each workbook, and configure the pivot tables the same. The problem I'm getting into is in regards to the "Strategy". I only want 11001and 11002 to show. All regions have these. However, there are tons of other strategies, and the mix differs by region. If have these these turned (as shown in my code below), I get an error if the value doesn't exist. If I don't turn them off, all show. How do I work around this?

ActiveSheet.PivotTables("PivotTable1").PivotFields("MFR_STRATEGY").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("MFR_STRATEGY")
        .PivotItems("11000").Visible = False
        .PivotItems("11003").Visible = False
        .PivotItems("11005").Visible = False
        .PivotItems("11007").Visible = False
        .PivotItems("11009").Visible = False
        .PivotItems("11010").Visible = False
        .PivotItems("11012").Visible = False
        .PivotItems("11100").Visible = False
        .PivotItems("11500").Visible = False
        .PivotItems("11900").Visible = False
        .PivotItems("13300").Visible = False
        .PivotItems("17700").Visible = False
        .PivotItems("18000").Visible = False
        .PivotItems("19000").Visible = False
        .PivotItems("20000").Visible = False
        .PivotItems("20200").Visible = False
        .PivotItems("20400").Visible = False
        .PivotItems("20600").Visible = False
        .PivotItems("21000").Visible = False
        .PivotItems("21100").Visible = False
        .PivotItems("22600").Visible = False
        .PivotItems("23200").Visible = False
        .PivotItems("23300").Visible = False
        .PivotItems("25000").Visible = False
        .PivotItems("25300").Visible = False
        
    End With

**barrboy89** · 05-11-2011, 11:25 AM

Set pvtTable = ActiveSheet.PivotTables("PivotTable1")

For Each pvtItem In pvtTable.PivotFields("MFR_STRATEGY").PivotItems
If pvtItem = "11001" or pvtItem = "11002" Then
pvtItem.Visible = True
Else
pvtItem.Visible = False
End If
Next

This could work?

**jomili** · 05-11-2011, 11:37 AM

I'm excited about the possiblity, but am having trouble implementing. The code below is my first attempt. Notice I dimmed pvtItem as PivotItem. When I try to run the code, it sticks on the line beginning "For each pvtItem..." with the error message

Run-time error '1004':Unable to get the PivotFields property of the PivotTable class.

Any help in getting through that is greatly appreciated.

Sub PivWork()
Dim LastRow As Long, Firstrow As Long
Dim Rng As Range
Dim Red As Range
Dim Pt As PivotTable
Set Pt = ActiveSheet.PivotTables("PivotTable1")
Dim pvtItem As PivotItem


'Change the pivot table to only give us the info we need
With Pt.PivotFields("FY 10-11 STRATEGY")
    For Each pvtItem In Pt.PivotFields("MFR_STRATEGY").PivotItems
                If pvtItem = "B.1.1" Or pvtItem = "B.1.2" Then
                    pvtItem.Visible = True
                Else
                    pvtItem.Visible = False
                End If
            Next

       ' .PivotItems("B.1.10").Visible = False
       ' .PivotItems("B.1.7").Visible = False
        '.PivotItems("B.1.8").Visible = False
        '.PivotItems("B.1.9").Visible = False
    End With

**jomili** · 05-11-2011, 12:12 PM

Never mind. Sometimes I'm just too thick to believe! "MFR_STRATEGY" is not the same thing as "FY 10-11 Strategy". That's what I get for trying to go from memory.

The code works great! That one's gonna go in my toolbox!

Thank you!