I had to add capability for our users to check/uncheck all
items in pivot table field. (excel 2000).
By searching this webisite I found the code that I needed,
but was having problems with setting items.visible to
True. Based on additional reading I found out that some
people resolved this problem by setting the sort for the
field manually. I tried to do that within the macro (set
sort to manual, check all, and then set sort back to what
it was), but excel is still complaining about it.

Please let me know if you can help.

Below is the macro itself

Thanks

--------------------------------------------------
Sub SelectAllItems()
Dim pt As PivotTable
Dim Items As PivotItem
Dim fieldName As String
Dim i As Integer, ICount As Integer, PCount As Integer,
CCount As Integer
Dim intASO As Integer
Dim pf As PivotField

Application.DisplayAlerts = False
Application.ScreenUpdating = True

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields(ActiveCell.Value)

With pt
CCount = 0
PCount = pf.PivotItems.Count
..ManualUpdate = True

intASO = pf.AutoSortOrder

If PCount 0 Then pf.AutoSort xlManual, pf.SourceName

For Each Items In pf.PivotItems
CCount = CCount + 1
If CCount PCount Then
Exit For
'This is done to exit before unchecking the last item.
One item must be left checked to prevent an excel error
End If

If Items.Visible < True Then Items.Visible = True

Next
..ManualUpdate = False
End With

pf.AutoSort intASO, pf.SourceName
Application.DisplayAlerts = True
Application.ScreenUpdating = False

End Sub
--------------------------------------------------

In my experience attempting to manipulate pivot tables in code (apart
from simple things like refresh or changing pages) has been a waste of
time. Even if things seem to work at the beginning they tend to fail
later.

Nowadays I just use pivot tables as they stand and link to other
sheets using VLOOKUP() etc. - when they really come into their own.
Sometimes this means having more than one table in a workbook, but the
stability is well worth the overhead of slightly additional file size.

Regards
BrianB
================================================



"Stan R" wrote in message ...
I had to add capability for our users to check/uncheck all
items in pivot table field. (excel 2000).
By searching this webisite I found the code that I needed,
but was having problems with setting items.visible to
True. Based on additional reading I found out that some
people resolved this problem by setting the sort for the
field manually. I tried to do that within the macro (set
sort to manual, check all, and then set sort back to what
it was), but excel is still complaining about it.

Please let me know if you can help.

Below is the macro itself

Thanks

--------------------------------------------------
Sub SelectAllItems()
Dim pt As PivotTable
Dim Items As PivotItem
Dim fieldName As String
Dim i As Integer, ICount As Integer, PCount As Integer,
CCount As Integer
Dim intASO As Integer
Dim pf As PivotField

Application.DisplayAlerts = False
Application.ScreenUpdating = True

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields(ActiveCell.Value)

With pt
CCount = 0
PCount = pf.PivotItems.Count
.ManualUpdate = True

intASO = pf.AutoSortOrder

If PCount 0 Then pf.AutoSort xlManual, pf.SourceName

For Each Items In pf.PivotItems
CCount = CCount + 1
If CCount PCount Then
Exit For
'This is done to exit before unchecking the last item.
One item must be left checked to prevent an excel error
End If

If Items.Visible < True Then Items.Visible = True

Next
.ManualUpdate = False
End With

pf.AutoSort intASO, pf.SourceName
Application.DisplayAlerts = True
Application.ScreenUpdating = False

End Sub
--------------------------------------------------