Hi, All.

I'm trying to determine which items are visible in each visible field in a
PivotTable called ptSumGraph:

For Each fld In ptSumGraph.PivotFields
For Each vfld In ptSumGraph.VisibleFields
If vfld.Caption = fld.Caption Then
For Each itm In fld.PivotItems
itm.Visible = True
Next itm
End If
Next vfld
Next fld

The error hits at itm.Visible = True

I've seen some other posts where setting the field's AutoSort property to
Manual is suggested, but I still get the same error.

Thanks so much for your help!

Jim

Jim,

If the hidden item has been removed from the source data, you will get the
error when you try to make it visible. Try changing this line

itm.Visible = True

to this

If itm.RecordCount 0 Then itm.Visible = True

--
Hope that helps.

Vergel Adriano


"JimRWR" wrote:

Hi, All.

I'm trying to determine which items are visible in each visible field in a
PivotTable called ptSumGraph:

For Each fld In ptSumGraph.PivotFields
For Each vfld In ptSumGraph.VisibleFields
If vfld.Caption = fld.Caption Then
For Each itm In fld.PivotItems
itm.Visible = True
Next itm
End If
Next vfld
Next fld

The error hits at itm.Visible = True

I've seen some other posts where setting the field's AutoSort property to
Manual is suggested, but I still get the same error.

Thanks so much for your help!

Jim

Vergel:

Thanks a bunch! Question - if I see that certain pivotitems in a field are
still tagged as hidden, that just means that there are no records
corresponding to those pivotitems? The error is gone, but I want to make
sure I'm getting the results I expect.

Thanks!

Jim

"Vergel Adriano" wrote:

Jim,

If the hidden item has been removed from the source data, you will get the
error when you try to make it visible. Try changing this line

itm.Visible = True

to this

If itm.RecordCount 0 Then itm.Visible = True

--
Hope that helps.

Vergel Adriano


"JimRWR" wrote:

Hi, All.

I'm trying to determine which items are visible in each visible field in a
PivotTable called ptSumGraph:

For Each fld In ptSumGraph.PivotFields
For Each vfld In ptSumGraph.VisibleFields
If vfld.Caption = fld.Caption Then
For Each itm In fld.PivotItems
itm.Visible = True
Next itm
End If
Next vfld
Next fld

The error hits at itm.Visible = True

I've seen some other posts where setting the field's AutoSort property to
Manual is suggested, but I still get the same error.

Thanks so much for your help!

Jim

Jim,

Yes, I believe the code would give you all items in the pivot table that has
at least one record in the source data. You might want to refresh the pivot
table first before going through the items. Also, I think you can do it with
just one for loop... give this one a try. It should give the same results as
your code

ptSumGraph.RefreshTable
For Each vfld In ptSumGraph.VisibleFields
For Each itm In vfld.PivotItems
itm.Visible = itm.RecordCount 0
Next itm
Next vfld


--
Hope that helps.

Vergel Adriano


"JimRWR" wrote:

Vergel:

Thanks a bunch! Question - if I see that certain pivotitems in a field are
still tagged as hidden, that just means that there are no records
corresponding to those pivotitems? The error is gone, but I want to make
sure I'm getting the results I expect.

Thanks!

Jim

"Vergel Adriano" wrote:

Jim,

If the hidden item has been removed from the source data, you will get the
error when you try to make it visible. Try changing this line

itm.Visible = True

to this

If itm.RecordCount 0 Then itm.Visible = True

--
Hope that helps.

Vergel Adriano


"JimRWR" wrote:

Hi, All.

I'm trying to determine which items are visible in each visible field in a
PivotTable called ptSumGraph:

For Each fld In ptSumGraph.PivotFields
For Each vfld In ptSumGraph.VisibleFields
If vfld.Caption = fld.Caption Then
For Each itm In fld.PivotItems
itm.Visible = True
Next itm
End If
Next vfld
Next fld

The error hits at itm.Visible = True

I've seen some other posts where setting the field's AutoSort property to
Manual is suggested, but I still get the same error.

Thanks so much for your help!

Jim