Adding Report Filters to Excel Pivot Tables via VB.

  • Thread starter Thread starter Andreas Lundgren
  • Start date Start date
A

Andreas Lundgren

Hi!

I'm writing my first VB script for Excel in order to generate nice
Pivot tables from loads of data.

When the Pivot table is generated, I want to add a "Report Filter" on
one parameter.

Straight forward macro after recording is the following; however, it
has a major problem:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields("Result").CurrentPage
= "FAIL"

The problem occurs when the Pivot field "Result" doesn't contain any
item with the value "FAIL", this causes a runtime error. (Sometime the
data is all correct, believe it or not... ;-) )

Is there a way to first check if it contains any "FAIL" (and then only
set "CurrentPage" if it does)?

Best Regards,
Andreas
 
hi Andreas,

With ActiveSheet.PivotTables(1).PivotFields("Result")
For Each pti In .PivotItems
If pti = "FAIL" Then .CurrentPage = "FAIL"
Next
End With
 
Back
Top