pivot row field definition in VBA

[Guest]

How do I write in VBA "I want the 1st row grouping of table X to be the field
name which is shown in cell A1"?

Assume that in A1, the name of the field has been selected. I want the user
to basically be able to select (outside of the pivot dropdowns) the name of a
field (State, Account, Rep) and have that field be the grouping in my pivot.
If there is a way to indicate what the second row is also, that would be good
(as then I can have them select which order of two different fields they want
to see).

I know that in this forum it sounds stupid, but for my users, learning how
to drag field names into an actual table to force the pivot to react is too
hard. So I am trying to work around it with simple dropdowns.

Thx for the help.

 

[Tom Ogilvy]

Turn on the macro recorder, then create a pivot table similar to what you
want using manual methods. Then turn off the macro recorder. Modify the
recorded code to get the value you describe from the sheet.

 

[Guest]

unfortunately, the logic flow when I record it is to put the field I indicate
into position 1. However, what I need is to then take position 2 (whatever
it is) and make it not exist anymore (effectively drag the field out of the
table). However, since this is a dynamic table, I don't know how to say take
"y" field, which is in position 2, and drag it out. When I record it, the
macro literally says "make y field invisible (knowing the name of y field)".
I need it to say "whatever position 2 is, make it invisible (hide).

Any help on how to do this?