VBA to move pivot table field name to rows

[Access Beginner]

Hi All,

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