Pivot Tables

[kathildreth2003]

Here is the code that I have used so that certain information will be shown within the pivot table that I am asking it to create ... The problem that I am having is that when one of these reasons is not included within the data the code does not work.

With ActiveSheet.PivotTables("PivotSummary4").PivotFields("Absence_Reason")
.PivotItems("Compassionate Paid").Visible = True
.PivotItems("Garden Leave").Visible = False
.PivotItems("Holiday").Visible = False
.PivotItems("Left").Visible = False

.PivotItems("Maternity UPD").Visible = False
.PivotItems("Not Abs").Visible = False
.PivotItems("Other Campaign").Visible = False
.PivotItems("Rest_Day").Visible = False
.PivotItems("Sick").Visible = True
.PivotItems("Unpaid Leave").Visible = True
.PivotItems("U/A").Visible = True
End With


--------------------------------------------------------
How can I get the code to include new reasons and skip reasons that do not exist.

 

[SkipVought]

Hi,

You could loop thru the PivotItems like this...

dim pvi as pivotitem
for each pvi in ActiveSheet.PivotTables("PivotSummary4").PivotFields("Absence_Reason").pivotitems
   with pvi
     select case .value
        case "Compassionate Paid", "Sick","Unpaid Leave","U/A"
           .visible = true
        case else
           .visible = false
     end select
   end with
next

Skip,
[sub]
[red][/red]
[/sub]