PivotTables: Restricting access to Report Filter Pages sheet data
 

Using Excel 2007.
I have a Pivot Table with a Report Filter and I've generated Pages sheets
for each of the values in the Report Filter field.
I want to send the pages sheets to different managers, and I want each
manager to see only the data that corresponds to his/her page sheet. Problem
is that while Pages look exactly how I want them to look, all the recipient
of the sheet has to do is change the filter to access all the underlying data.
I found a reasonably efficient way to do this on a small number of Pages:
1. Drill down on the Report Filter Page Grand Total value to generate a new
sheet with all detail records for the filtered page.
2. Since Excel 2007 creates a table for each drill-down sheet, I then
simply go back to the original Report Filter Page sheet and Change the data
source to be the table from the drill down in step 1.
3. Then I delete the drill-down sheet, leaving just the Report Filter page
sheet with its PivotTable adjusted to access only the data I want it to
access, which I can send out to the appropriate manager.
This is great if there are only 2 - 3 pages, but it's not viable when there
are dozens or hundreds of pages. Any suggestions as to how a PivotTable user
who's weak on VBA coding can improve / automate this process?