Pivot Table List Order

[hatstand]

Hi All,

I have tried and failed to work out how to modify this recorded line of code to allow me to use variables. The line is based on reordering a pivot table hierarchical field into the order that I want. I’m doing this as I can’t use Custom Lists as the list far exceeds the limit allowable.

What I’m after is to allow ‘Football’ and 'Position' to be variables, so when I loop the line I can say what position in the list the variables should be. Football position 1, Tennis position 2 etc.

Any pointers would be much appreciated.

ActiveSheet.PivotTables("PivotTest").PivotFields("[Respon_Table].[Responsibilities].[Sport]").PivotItems("[Respon_Table].[Responsibilities].[Sport].&[Football]").Position = 1

 

[hatstand]

Hi All, Isn't it always the case when you ask for help and work it out, sorry. This is how I did it, simple. Thanks

Dim i As Integer
Dim n As String

i = 1
n = "A"

'Column 0.................................................................................

ActiveSheet.PivotTables("PivotTest").PivotFields("[Respon_Table].[Responsibilities].[Sport]").PivotItems("[Respon_Table].[Responsibilities].[Sport].&[" & n & "]").Position = i