Pivot - locking PageField

[cjorgensen]

Hi,
I have a large dataset that I would like to create a pivot from. I need
to distribute the file to several people. The data is sensitive, and
therefore each person should only be able to view the data they are
responsible for.

Is there any way to lock pagefields, so that e.g. John D. only can view
sales statistics for his area?

If this is possible I would save huge amount of time, not needing to
split the data before creating the pivots!

 

[Debra Dalgleish]

If the data is sensitive, it would be better to use an Advanced Filter
to extract each person's data to a new workbook, and create a pivot
table there. There's information here on Advanced Filters:

http://www.contextures.com/xladvfilter01.html

However, you can put the Area field in the Page area of the PivotTable
and choose a name from the dropdown, to see only that area's data. Then,
use the following code to disable selection from the page field.

Users will still be able to drag the Area field to the row area, and see
all the data:

Sub DisablePageSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PageFields
pf.EnableItemSelection = False
Next
End Sub