Adjusting PTable Positions

[Joe C]

I was hoping to do something below to a headcount pivotable report I develped



For x = 6 To ENOPT
If IsEmpty(Cells(x, 7)) = False And IsEmpty(Cells(x, 4)) = True Then

b = ActiveSheet.PivotTables("SalTa1").PivotFields("POSN_TITLE").PivotItems( _
Cells(x, 6)).Position
ActiveSheet.PivotTables("SalTa1").PivotFields("POSN_TITLE").PivotItems( _
Cells(x, 6)).Position = b - 1
End If

Next x

But it is not working
What I want to do is
If my if statemtment triggers action I want to click on cell (x,6) and move it up one positon.

 

[Andrew Poulsom]

In what way isn't it working?

 

[Joe C]

It says

Unable to get pivotitems property of the pivotfield class

 

[Andrew Poulsom]

What's in Cells(x, 6) - is it a valid PivotItem?

 

[Joe C]

If I just move using mouse and recorder this is the code I get


Range("F38").Select
ActiveSheet.PivotTables("SalTa1").PivotFields("POSN_TITLE").PivotItems( _
"SR. EXECUTIVE MANAGER").Position = 161

I was hoping to tell this item to be position 160


Keep in mind that SR. EXECUTIVE MANAGER itselef is not unique. Thanks for your help Andrew, and if you think I am just going the wrong way just let me know.

 

[Andrew Poulsom]

If Excel can't get the PivotItem it doesn't exist.