Pivottable: move "∑ values" label between row labels and column labels through VBA

[Need_help_please]

Hi there,

If you look at the pivottable in this link:
https://www.nothingbutsharepoint.com...le-06_1481.png

you'll see that the "∑ values" label is in the column labels box.
However, when I create my own pivottable through vba code, that label is placed in the row labels box.
Is it possible to move it to the column labels with vba code?

Thanks in advance.

[Need_help_please]

omg, too easy.

The name of the label is "data". With that name you can change the pivotfield orientation between xlrowfield and xlcolumnfield.

Simple

[DifferentFrogs]

Can anyone explain what the above post means? I'm having the exact same problem but am unable to divine the code.

Thanks!

[DifferentFrogs]

Still desperate for the answer (spent the last hour looking); anyone have any ideas?

[richgarrod]

I know this thread is dead but given it ranks on Google I figured it would help if people got the answer in the future:

' Create pivot table
Set objtable = ActiveSheet.PivotTableWizard
ActiveSheet.Name = "cost rev analysis"

' Add Cost in as a data metric
Set objfield = objtable.PivotFields("Cost")
objfield.Orientation = xlDataField

' Add the period as a row variable
Set objfield = objtable.PivotFields("Period")
objfield.Orientation = xlRowField

'Add your revenue metric in as a data field. THIS IS WHERE I GOT VALUES IN THE ROW INSTEAD OF COLUMN FIELD
Set objfield = objtable.PivotFields(revenuemetric)
objfield.Orientation = xlDataField

' This line rearranges the "Data" to the column field, Data is essentially Values.
Set objfield = objtable.PivotFields("Data")
objfield.Orientation = xlColumnField

The first response put me onto this but could have been cleaer thanks though!

Rich

[dswinsto]

I know this thread is dead but given it ranks on Google I figured it would help if people got the answer in the future:

' Create pivot table
Set objtable = ActiveSheet.PivotTableWizard
ActiveSheet.Name = "cost rev analysis"

' Add Cost in as a data metric
Set objfield = objtable.PivotFields("Cost")
objfield.Orientation = xlDataField

' Add the period as a row variable
Set objfield = objtable.PivotFields("Period")
objfield.Orientation = xlRowField

'Add your revenue metric in as a data field. THIS IS WHERE I GOT VALUES IN THE ROW INSTEAD OF COLUMN FIELD
Set objfield = objtable.PivotFields(revenuemetric)
objfield.Orientation = xlDataField

' This line rearranges the "Data" to the column field, Data is essentially Values.
Set objfield = objtable.PivotFields("Data")
objfield.Orientation = xlColumnField

The first response put me onto this but could have been cleaer thanks though!

Rich

This is the perfect line I've been looking for!! Thanks!

Set objfield = objtable.PivotFields("Data")
objfield.Orientation = xlColumnField

[broderone]

Great post! Thanks for the answer, I've been looking it for hours!

[TFT2012]

Rich, Thank you so much for the soution.
I came here via from Google and had been struggled for many hours. Finally I got the solution.

For any others may have same issue and visit this post from Google, the answer is on Rich's post.
In order to follow the privacy rule of this website, just take few seconds to register.

Thanks again, Rich!

[rgsakhalkar]

Thank you so much for the solution.

[tomhodson]

thanks for this. v helpful