Excel Pivot Table VBA: How to put Sums in Row Labels

Question

I have am trying to create a Pivot Chart from a set of test data. It is almost working, but to show the chart the sum of data has to be in the Row Label Box.

I can do it easily by hand, but this is supposed to work using VBA. The rest of the table and chart is being generated using VBA and it works well, but I don't know how to move the SUM field into the Row Labels

Move Sum of Data from Column Label to Row Label:

sourceSheet.Select
Set dataRange = sourceSheet.Range(sourceSheet.Cells(startRow, 1), sourceSheet.Cells(currentRow - 1, startColumnVals + columnArraySize - 1))
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pTable = pCache.createPivotTable(pivotSheet.Range("A42"))

' Page fields are the filters
Set pField = pTable.PivotFields("Result")
pField.Orientation = xlPageField

' Column fields will apprear in the Legend
Set pField = pTable.PivotFields("TestName")
pField.Orientation = xlColumnField


' Row Fields - don't know what to do here !

' Data field
For i = 0 To (columnArraySize - 1)
    cellText = sourceSheet.Cells(startRow, startColumnVals + i).Text
    Set pField = pTable.PivotFields(cellText)
    pField.Orientation = xlDataField
    pField.Name = "S" & cellText & "-" & i
    pField.Function = xlSum
Next

Set dataRange = pTable.TableRange1

Set pChaObj = pivotSheet.ChartObjects.Add(Left:=10, Width:=1000, Top:=15, Height:=500)
Set pChart = pChaObj.Chart

pChart.SetSourceData dataRange
pChart.ChartType = xlLine

Raunak Thomas Raunak Thomas · Accepted Answer · 2018-04-30T12:58:00

Is this what you are looking for?

Set pField = pTable.PivotFields("value")
pField.Orientation = xlRowField

Excel Pivot Table VBA: How to put Sums in Row Labels

1 Answers