Order of Pivot Table items

[tristanf]

I have a list of sales data, including categories and the products within each category. I am trying to specify the order in which the products appear in the pivot table. However, when a product doesn't exist in the data, I get an error. Is there a way to skip over a product if it doesn't exist in the data, ie if Product 2 is not found, then move on to Product 3?

<CODE>
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Category"). _
PivotItems("Product1").Position = 1

ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Category"). _
PivotItems("Product2").Position = 2

ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Category"). _
PivotItems("Product3").Position = 3
etc...
</CODE>


Excel 2007, Windows 7

Thanks for your help!

 

[Andrew Poulsom]

Untested:

    Dim Pos As Long
    Pos = 1
    On Error Resume Next
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Category"). _
        PivotItems("Product1").Position = Pos
    If Err = 0 Then
        Pos = Pos + 1
    Else
        Err.Clear
    End If
    On Error GoTo 0
    On Error Resume Next
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Category"). _
        PivotItems("Product2").Position = Pos
    If Err = 0 Then
        Pos = Pos + 1
    Else
        Err.Clear
    End If
    On Error GoTo 0
    On Error Resume Next
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Category"). _
        PivotItems("Product3").Position = Pos
    If Err = 0 Then
        Pos = Pos + 1
    Else
        Err.Clear
    End If
    On Error GoTo 0
    On Error Resume Next
'   Etc

 

[tristanf]

Works like a charm... Thanks a million!