Filtering Excel

[James]

Hello,

I have an Excel Worksheet with a Pivot Table at the top
with a Filter and a data set returned from a DB2 database
below it. I would like to filter the DB2 data set based
on the Pivot Table filter that is enabled. Any ideas?

Thanks!

 

[Debra Dalgleish]

As answered in .excel.misc --

You can use a Worksheet_Calculate event to change the AutoFilter, based
on the PivotTable page selection. In the following example, the page
field is "Region", the table being autofiltered starts in cell A21, and
field 5 is filtered:

'============================================
Dim mvPivotPageValueAll As Variant

Private Sub Worksheet_Calculate()
'variation on code by Robert Rosenberg 2000/01/11
''a module level variable (see above) keeps track of
''the last selection from the Page Field.
''This routine is placed in the Worksheet
''containing the PivotTable's code module.
Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables(1)
If LCase(pvt.PivotFields("Region").CurrentPage) _
<> LCase(mvPivotPageValueAll) Then
Application.EnableEvents = False
If pvt.PivotFields("Region").CurrentPage = "(All)" Then
Range("A21").AutoFilter Field:=5
Else
Range("A21").AutoFilter Field:=5, _
Criteria1:=pvt.PivotFields("Region").CurrentPage.Value
End If
mvPivotPageValueAll = _
pvt.PivotFields("Region").CurrentPage
Application.EnableEvents = True
End If
End Sub
'=========================================