Filtered pivot table reports - need to change cache?

[MichelleB]

Hi all,
I have a large set of data that I have put into a single pivot table, then I use Report Filter Pages to separate into 12 pages. I need to send the data to 12 different users and have them be able to see the details of their data but not be able to see data for the other 11 users. When I copy one of the 12 pages into a new workbook, I still have the ability to go to the report filter and by clicking on Select All, see the data. If I copy the pivot table to a new workbook using Paste Values, then the data details are not available.

I did some Googling and found posts on unlinking a pivot table and its data cache, but none of those techniques seemed to work in my version of Excel (2010). The instructions said to select a cell in the 'child' pivot table and open the Pivot Table wizard, but I get errors when I try to do that.

Thanks for any assistance.

 

[rrocker1405]

Hi Michelle,

Attached is a sample sheet for your requirement and I hope this solves your question. Having said the below, I have put together a different approach which avoids pivot table.

1. Create a template with data which can pull the user specific data using index and match. The user information can be in a dropdown like in the attached sample sheet.

2. Once the above is created select the user information from the drop down to populate user specific information and use the send button to send the range to the specific user. there is a sample code i have placed in the document [please change the range and email id]. for more information have a look at:
http://www.rondebruin.nl/win/s1/outlook/mail.htm

3. Once the users complete the data entry, use the consolidation option using the code stated below to gather information as per your choice and report accordingly.
http://en.kioskea.net/faq/24666-excel-vba-copy-data-to-another-workbook

Hope this helps

Regards,
A!