Pivot Table PivotFields.Position returns Error 2042

Question

I am using VBA to capture all the areas put in a pivot table. The code is below

For Each objCubeFld In pvt.PivotFields 'Loop through all fields in PivotTable
Cubefield(i) = objCubeFld
Orient(i) = objCubeFld.Orientation 'Orientation
Pos(i) = objCubeFld.Position 'Position
i = i + 1
Next objCubeFld

However, I got below error.

I know when Orientation="3", it means filter. After removing the filter, the program runs fine. Could anyone explain this?

@skkakkar No. Any field will fail, not just Date. I just tested. — NewGuyComesIn, May 26 '16 at 15:59
I have very recently started learning pivot tables, As I have gathered from web. Field.CurrentPage only works for Filter fields (also called page fields). If you want to filter a row/column field, you have to cycle through the individual items, like so: ~ With Field If .Orientation = xlPageField Then .CurrentPage = Value ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField [Then SO Question Reference~](http://stackoverflow.com/questions/37464954/pivot-table-pivotfields-position-returns-error-2042?noredirect=1#comment62430216_37464954) — skkakkar, May 26 '16 at 16:33
@skkakkar Thanks for the answer. I think you are referencing [This SO](http://stackoverflow.com/questions/11071662/filter-excel-pivot-table-using-vba). — NewGuyComesIn, May 26 '16 at 17:31
Kindly share the link for my learning or solution hint. Thanks — skkakkar, May 26 '16 at 17:32
Yes it is the same link I was referring to. Look forward to your solution hint. — skkakkar, May 26 '16 at 17:35

score 0 · Answer 1 · answered May 26 '16 at 17:34

This is no .Position when it comes to filter or Page field. Just not use .Position.

  For Each objCubeFld In pvt.PivotFields 'Loop through all fields in PivotTable
    Cubefield(i) = objCubeFld
    Orient(i) = objCubeFld.Orientation 'Orientation
    If objCubeFld.Orientation = "3" Then 'When it is filter
    Pos(i) = 1
    Else
    Pos(i) = objCubeFld.Position 'Position
    End If
    i = i + 1
    Next objCubeFld

Pivot Table PivotFields.Position returns Error 2042

1 Answers1