Adding Report Filters to Excel Pivot Tables via VB.

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
 
I

isabelle

hi Andreas,

With ActiveSheet.PivotTables(1).PivotFields("Result")
For Each pti In .PivotItems
If pti = "FAIL" Then .CurrentPage = "FAIL"
Next
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

Top