Pivot Table Fields (XL2000 SR1)

Pivot Table column fields have a drop down list so that selected fields only can be displayed. If there are many fields and you want to display only a few of them, it is very tedious unchecking dozens of boxes. Is it possible to write a macro to uncheck the whole list so you can then select the few entries you do want to display?

Re: Pivot Table Fields (XL2000 SR1)

In Excel 2002, the first item in the list is "(Show All)", you can use this to check/uncheck all items. I assume this is not available in Excel 2000.

You can't uncheck the whole list in a macro, at least one item must remain visible. (You can uncheck the whole list interactively, of course, but clicking OK causes an error message then.)

Perhaps you can adapt the following macro to your needs. I haven't done anything to generalize it.

Sub ClearAllPivotItemsExceptOne()
Dim i As Integer
' Replace "PivotTable1" and "Month" by the appropriate names
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Month")
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next i
End With
End Sub

Re: Pivot Table Fields (XL2000 SR1)

Hans, "Show All" is not available in XL2000.

I was hoping the macro would uncheck all boxes but leave the list open so you can check one or more before pressing OK, but I guess that is probably not possible; perhaps unchecking all except the first field would be a more realistic aim.

Re: Pivot Table Fields (XL2000 SR1)

Michael,

I am attaching workbook which uses a rough and ready userform to attempt to achieve what you want. When you invoke the userform you can select the Pivottable, the Field you wish to work with, and then select items individually or in extended mode using the control key. You can also "Select All", but cannot proceed unless there is at least one visiblle item in the field.

Re: Pivot Table Fields (XL2000 SR1)

Your solution does just what I want Andrew, however I cannot get it to work from Personal.xls.

I have dragged a copy of the form into the forms section of Personal.xls, and copied and pasted all the code into a blank module in Personal.xls, then deleted your file so there is only one copy of the form and macro left.

When I go into my sheet with a Pivot Table and call Macros from the Tools menu, the macro appears as

Personal.xls!'[Module1 (2)].HidePivItems

and the Run button is grayed out, whilst other macros appear without the module name in their names and with the Run button not grayed out.

If I edit the module name out of the HidePivItems macro, the Run button becomes active but when I press it I get the error message

Re: Pivot Table Fields (XL2000 SR1)

Michael,

It seesm to me that you have more than one instance of HidePivItems on your system and that is why the macro name includes the workbook and module name. Search your entire Personal.xls VBA Project for HidePivItems, or make sure that Personal.xls does not contain any reference to any file with that sub name.