Pivot VBA: Inserting Row label on last position before the data values start.

[hitxrafa]

Hi,
I am not expert on VBA, I can only do the very basics and record the commands.
I have the following code:

Please Login or Register  to view this content.

What I want to do is insert the desired field on the last column position before the data values start on the pivot. So I don't know which condition I have to apply if I don't want this field's position to be binded to a fixed number like 3 on the code.

Thanks in advance!

[JosephP]

if you remove the .position line the default should be to add the field as the last row field

[hitxrafa]

Yeap That's right!
I was looking all over internet for hours and couldn't find any problem like mine. Thanks!
Regarding the same code I have another issue. You know that we can play around with the table, adding fields as row labels, column labels,etc. I wanted to create a button to reset this changes that people might do to a standard layout of the pivot.
Here is a sample of what I have now

Please Login or Register  to view this content.

As shown, it removes the Plant Supplier that we inserted on the first code above, whatever the position is as row label. But the thing is I have this pattern that I call standard pivot and people might work around it and get some report filters and put them as row labels and vice-versa.

Is there any solution instead of clearing all the contents and starting from 0, and doing all the formatting once more...

Thanks again!

[JosephP]

not really-you can use the addfields method of the pivot table to replace all row and column fields with the ones you want but for filter/page fields and data fields it's easiest to simply clear the table and start again

[hitxrafa]

Ok thanks anyway.. thats what I thought.
Could you send me the link if there is any to this addfields method?

[JosephP]

look it up in the VBA help files and/or the object browser ;-)

[hitxrafa]

Alright!
Last question.. I was trying to insert the Report filters back on once they were hidden from the row labels and it somehow doesn't work...

Please Login or Register  to view this content.

Is it possible to build the report filters via code? because they are not working

[JosephP]

you have a typo-you have x1PageField but it should be xlPageField (with L instead of 1)

[hitxrafa]

Sweet! Thanks dude.. You were very helpful with my stupid, newbie questions :D

[JosephP]

you're welcome :-)

please don't forget to mark the thread solved