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

Similar Threads

Linking 3 auto filters to a pivot table on another worksheet 2
Several Pivot Tables: Change one filter and adapt it for the other 2
Multiple Pivot Tables Expand to Fit 0
Update Pivot Table Filters based on a Cell Value in Excel 2007 0
Change report firlds in pivot tables and charts with one selection 0
Using macro to switch 'page' in a pivot table? 1
Format Pivot total rows 2
Pivot Table Change to US Date Format - Why? 2

Top