PivotItem Problem !

[Marc]

Hello,

Here is my problem :

My macro get data from a dynamic table .

For each PivotItems in PivotFields("color"), i get the
number or sum in the data range. Everything works well
the first time but not after.

If a data of one pivotItem is empty, the PivotItem is still on the
PivotItemS list on the Table. So my loop "For each" return Error when i use
the PivotItem LabelRange function , because there is no column
(for ex) for that PivotItem.

Is there a way to have only real value one that PivotItems.
I try the command PivotCache.Refresh but still the same error.

Did i explain well my prob ?

Tia,


A sample of my code:
With Worksheets("tableau").PivotTables("TCD_emb")
.RefreshTable
Dim i As Integer
i = 10
For Each pvi In .PivotFields("C").PivotItems
If pvi.Name = "(vide)" Or Not pvi.ShowDetail Then
Else
c = pvi.Name
Qte = pvi.LabelRange.Offset(1, 0).Value
NbPoste = pvi.LabelRange.Offset(2, 0).Value


Etc ...
....

 

[Debra Dalgleish]

There are instructions here for clearing old items from a PivotTable
dropdown list:

http://www.contextures.com/xlPivot04.html

You could run that code before your labels macro.