Excel - Pivot Table Position 1

[enbw]

Hi,

I am writing a macro which generates a pivot table from quite large sheet.

I need the macro to move the pivot fields within the pivot table so that a particular item is always last, furtherest to the right. When I generate a macro through excel, recording, it generates the following;

ActiveSheet.PivotTables("PivotTable5").PivotFields(" STATUS ").PivotItems( _
"Budget").Position = 9

Is there a variable such as 'last' to use instead of 9. So there irrespective of the number of items this field is always the last? Such as the option within the pivot table.

Thanks in advance.

 

[KenWright]

Have you tried creating a variable that represents a count of all the PivotItems and then using that within the positioning statement, which should effectively give you the same thing wouldn't it? eg (Pseudo code only as haven't tried it)

cnt = ActiveSheet.PivotTables("PivotTable5").PivotFields(" STATUS ").PivotItems.count

ActiveSheet.PivotTables("PivotTable5").PivotFields(" STATUS ").PivotItems("Budget").Position = cnt

Regards
Ken............






----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Adnane]

Please accept a star from me Ken.

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[KenWright]

Cheers Adnane - Hopefully that means it worked

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------