Force All Pivot Field Items Selected Excel 2003

[rickincanada]

I have a pivot table which I want to force the all of the pivot table items to be selected for a particular pivot table field. One would think that this would be as easy as unlocking all cells on the sheet with the exception of this pivot field and then locking the worksheet. This doesn't work though as I am generating multiple pivot tables on the same workbook for the same range and I get this message: "this command cannot be performed while a protected sheet contains another PivotTable report based on the same data source...".

My thinking is that I can do something along the lines of this:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim oPI As PivotItem

Application.ScreenUpdating = False

If Target.PivotFields("Item Sold").PivotItems.Count <> Target.PivotFields("Item Sold").VisibleItems.Count Then
For Each oPI In ActiveSheet.PivotTables("PivotTable1").PivotFields("Item Sold").PivotItems
oPI.Visible = True
Next oPI
End If

Application.ScreenUpdating = True
End Sub

This is failing right away though on the If Target.Pivot.... line. Any help you can offer would be great!!

 

[Jerry Sullivan]

You'll need to have your code do this sequence of steps:

1. Unprotect the Sheet
2. For each PivotTable, make all PivotItems Visible in the "Items Sold" PivotField
3. Protect the Sheet

You'll probably have better results putting this in a macro that you could call with a button instead of trying to use the Worksheet_PivotTableUpdate Event to trigger those 3 steps.

Is your "Items Sold" field a Report Filter(PageField), or a Row Label field?