One report filter for multiple worksheets

I'm trying to find a way to use report filters for a pivot table in one worksheet and apply the same filters to 5 other worksheets in the workbook that contain similar data. In other words, when I select the report parameters (customerID,start date, end date) I want the pivot tables on the remaining worksheets to use the same parameters to populate the corresponding pivot tables and charts.  I'm using Excel 2007/Windows.
Who is Participating?
hitsdoshi1Connect With a Mentor Commented:
For eg...on Sheet1 is:
A1: CustomerID
A2: StartDate
A3: End Date

And you have Pivots on Sheet2, Sheet3, Sheet4 and so on.....

Following code will do the job...

Sub UpdatePivots()
Sheets("Sheet2").PivotTables("Pivot1").PivotFields("CustID").CurrentPage = Sheets("Sheet1").Range("A1").Value
Sheets("Sheet3").PivotTables("Pivot2").PivotFields("CustID").CurrentPage = Sheets("Sheet1").Range("A1").Value
Sheets("Sheet4").PivotTables("Pivot3").PivotFields("CustID").CurrentPage = Sheets("Sheet1").Range("A1").Value

Sheets("Sheet2").PivotTables("Pivot1").PivotFields("StartDt").CurrentPage = Sheets("Sheet1").Range("A2").Value

And so on...

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.