Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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.
0
Ed_CLP
Asked:
Ed_CLP
1 Solution
 
hitsdoshi1Commented:
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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now