Pivot Report Filter

[ls010s]

Hi
I need some help with the following:

I have 4 sheets in my excel workbook. Sheet1 is blank, sheet2 has a pivot, sheet3 has a pivot and sheet4 has the raw data for the pivot.

I use the pivot to view sales by agent (we have 35). I want to put the agent number on sheet1 manually and want the report filter on sheet2 and sheet3 to pick that number up and report against that agent number.

Is there a function I can use or can you please help out with a VBA code to achieve this? I use Office 2007.

Regards

 

[sdsurzh]

Hi,


2 things you have change before running this macro 1. Instead of pivotTable5 ".PivotTables("PivotTable5")" you should replace your pivottable names for pivotTable5 and pivotTable6
2.Instead of Event Year "PivotFields("Event Year")" you should replacce your pivottable names for both pivotTables.




create a module and paste the below code
sub checkdata


Worksheets("Sheet1").Select
Range("a1").Select
temp = ActiveCell.Value


Worksheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields("Event Year").PivotFilters. _
Add Type:=xlCaptionEquals, Value1:=temp

Worksheets("Sheet3").Select
ActiveSheet.PivotTables("PivotTable6").PivotFields("Event Year").PivotFilters. _
Add Type:=xlCaptionEquals, Value1:=temp
End Sub

Thanks,
SK