PDA

View Full Version : [SOLVED] VBA to move pivot table field name to rows



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

Paul_Hossler
01-31-2015, 07:32 PM
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
01-31-2015, 08:04 PM
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
02-02-2015, 01:12 AM
Thanks work your solution worked a treat

Cheers
Haydn