Getting rid of "(All)" from a Pivot Table in Excel2000

[macrus]

Has anyone come across a way to remove the automatically generated entry in Page variables of Pivot Tables called (All)?

We need to remove this so that users don't select this options as it makes the results non-sensical.

VBA or other solutions would be greatly appreciated. D

 

[xlbo]

Unfortunatley, I don't think you can do this as an option as (All) is merely the absence of a selection. The only way round it that I've found is to use the worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Set mTest = Intersect(Range("E1&quot, Range(Target.Address))
If Not mTest Is Nothing Then
'in pivot table
If mTest = "(All)" Then
With PivotTables(1).PivotFields("Letter&quot
.CurrentPage = .PivotItems(1).Name
End With
Else
End If
Else
End If
End Sub

where E1 is the cell ref of the page field
and "PivotFields("Letter&quot" is the page field name

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde