I have an excel spreadsheet that contains about twenty tabs and each tab contains one or two pivot tables that represent the sales data for an individual sales rep. I also have a macro that sends each tab (pivot table containing sales data) to the correct sales person.
The problem that I have is with the security of the pivot tables and the separate excel sheet that contains the data (the entire teams sales). To prevent a sales person from seeing other peoples sales, I have created a macro to disable all of the features of a pivot table with the exception of the drill down feature (a feature that I need to have). I then password protected this macro. However, if a sales rep is able to get a program to guess my vba password, they will be able to reverse my macro and reverse my filters to see the data for the entire team.
I was thinking that a solution to this problem would be to update the macro that sends the email to the individuals. I think I would need to create something that restricts the data that is drillable to only what is filtered on the pivot table. Does anyone know how to create a macro that would allow me to send an email to each sales person, but also restrict the pivot tables to only what is filtered. I do not want the sales people to see others information from sales they are not involved in. If you have any ideas on how I can do this or any other possible solutions, please let me know.
Bookmarks