Limit Pivot Chart Data

Question

I use the following code to generate a pivot table

    Sheets("Raw Data").Select

    Range("A1").Select

    Selection.End(xlDown).Select

    lstrow = Selection.Row

' Generate Pivot Table

    Cells.Select

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

        "'Raw Data'!A1:P" & lstrow).CreatePivotTable TableDestination:="", TableName:= _

        "PivotTable1", DefaultVersion:=xlPivotTableVersion10

    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

    ActiveSheet.Cells(3, 1).Select

    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Data", _

        ColumnFields:="Succ"

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count")

        .Orientation = xlDataField

        .Caption = "Sum of Count"

        .Position = 1

        .Function = xlSum

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Parts")

        .Orientation = xlDataField

        .Caption = "Sum of Parts"

        .Position = 2

        .Function = xlSum

        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Labor")

        .Orientation = xlDataField

        .Caption = "Sum of Labor"

        .Position = 3

        .Function = xlSum

        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Misc")

        .Orientation = xlDataField

        .Caption = "Sum of Misc"

        .Position = 4

        .Function = xlSum

        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total WIP")

        .Orientation = xlDataField

        .Caption = "Sum of Total WIP"

        .Position = 5

        .Function = xlSum

        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("L.L.")

        .Orientation = xlDataField

        .Caption = "Average of L.L."

        .Function = xlAverage

        .NumberFormat = "0"

    End With

This make 1 big pivot table with all the information.  Now I have been asked to, instead of making one big one, make 3. (1) all the data except for 35,41 (2) only 35 (3) only 41.

Now I figured out that I can add the following code to exclude 35,41 from my main pivot table

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Succ")

        .PivotItems("35").Visible = False

        .PivotItems("41").Visible = False

    End With

But How can I make another pivot table, but limit it to only 35, not knowing how many other "succ" will be in the raw data?  Is there a way to llop through the PivotItems for a specific PivotFields()?

Thank you,

QB

Answer 1

For anyone else trying to figure this one out.  I managed to accomplish it by using:

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Succ")

        For Each PivotItem In .PivotItems

            If PivotItem <> "35" Then

                PivotItem.Visible = False

            End If

        Next PivotItem

    End With

There may be a better method, but this one does work!

Answer 2

Glad to know that the issue was fixed.