Adding Report Filters to Excel Pivot Tables via VB.


Andreas Lundgren


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
= "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,


hi Andreas,

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question