How can I force Excel to place Sigma Values pivot field into the Columns Label using VBA code?

0
votes

In my Pivot Tables, I prefer to see the Value fields displayed as column labels next to the Row label fields. By default, Excel will create a field "Sigma Values" and move it into either the Row Labels or Column Labels. I would like to add to my Pivot Table Create macro, a line(s) of code to create and place this field into the Columns Label. Below I show the orientation I would like. Thanks in advance! The Sigma Values field is created by default. I would like to force excel to always place this field in Column Label

Here is my VBA code, it is still not finished

    Sub olderRTSPivot()

    Dim objTable As PivotTable, objField As PivotField

'point to top left of raw data table (ie the Exception Report )

     ActiveWorkbook.Sheets("Sheet Name").Select
     Range("A1").Select

'tell Excel to use this table to create the pivot

     Set objTable = ActiveSheet.PivotTableWizard

'set Filter fields

     Set objField = objTable.PivotFields("Aging Category")
     objField.Orientation = xlPageField

     Set objField = objTable.PivotFields("Project")
     objField.Orientation = xlPageField

'Set Row Fields

     Set objField = objTable.PivotFields("Originator")
     objField.Orientation = xlRowField

     Set objField = objTable.PivotFields("Created")
     objField.Orientation = xlRowField

'Set Data Fields

     Set objField = objTable.PivotFields("RTS")
     objField.Orientation = xlDataField
     objField.Function = xlCount

     Set objField = objTable.PivotFields("Days Aged")
     objField.Orientation = xlDataField
     objField.Function = xlAverage

     Set objField = objTable.PivotFields("Days Aged")
     objField.Orientation = xlDataField
     objField.Function = xlMax

     End Sub
1
excelvbapivotpivot-table

1 Answers

0
votes

You can set DataOnRows property to False

pivotTable.DataOnRows = False