Lplater
01-30-2015, 11:27 PM
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
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