Locking Report Filter on Pivot Table

Question

We have a large amount of data (payroll) that we are pivoting and filtering by Cost Center to report wages by Cost Center and sending them out individually. When we filter by Cost Center and each Cost Center's information is exported to its own sheet, you are still able to use the filter to select a different cost center. Obviously we do not want each cost center to be able to view other people's wage information, and therefore would like to lock the Report Filter.

Hiding the row, and locking/protecting the sheet is very manual as there are a hunderd cost centers, and plus, using macros, you can easily break the password, so this is not a viable option.

Any ideas?

Answer 1

Hi,

Al I can suggest is Copying and then Pasting Special as Values all information on the sub sheets that you have created.  Since they will no longer be Pivots, Report filter will not exist.

Answer 2

You are welcome.  If my solution helped, please mark it as Answer.

Answer 3

Thanks. This seems to be the best alternative. And because I can select all sheets in the workbook and Copy, Paste Special simultaneously for all of them, this is a sufficient work around. Thank you for your response.