VBA to move pivot table field name to rows

[Lplater]

I have posted this question on another forum without luck, so I thought I'd try here. I hope this is not against any rules, if so my apologies in advance.

I have a bit of code that will move a field name in a pivot table to Rows. When I record the macro this field name is hard coded ( see example below).

What I'd like to do is have the hard code value replaced with a value in a cell, so I can have data validation in this cell and change it rather than going into the code each time.

In the 2nd example below I've tried something which I thought might work, but sadly it doesn't.


Snipet of existing code

With ActiveSheet.PivotTables("ReasonPivotTable").PivotFields("Reason name")
        .Orientation = xlRowField
        .Position = 1
    End With

Snippet of code I'm trying to replace it with

Dim PtableRowI As Variant


Set PtableRowI = Sheets("DataRequestPt").Range("E3")


With ActiveSheet.PivotTables("ReasonPivotTable").PivotFields.(PtableRowI)
            .Orientation = xlRowField
            .Position = 1
End With

[Paul_Hossler]

Not tested but give this a shot

Dim PtableRowI As String

PtableRowI = Sheets("DataRequestPt").Range("E3").Value

With ActiveSheet.PivotTables("ReasonPivotTable").PivotFields.(PtableRowI)
            .Orientation = xlRowField
            .Position = 1
End With

[Lplater]

Thanks Paul, it's Sunday here at the moment and I will try this out tomorrow and let you know how it goes.

Cheers
Haydn

[Lplater]

Thanks work your solution worked a treat

Cheers
Haydn