I am using the code below to create a pivot table with vba.
Code: Select all
strField = "Converted YTD GBP Balance"
Set wshSrc = ActiveSheet
Set rngSrc = ActiveCell.CurrentRegion
Set wshTrg = Worksheets.Add(After:=wshSrc)
wshTrg.Name = wshSrc.Name & " Pivot"
Set rngTrg = wshTrg.Range("A3")
Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=rngSrc)
Set pvt = pvc.CreatePivotTable(TableDestination:=rngTrg)
pvt.AddFields RowFields:="Nominal", ColumnFields:="Company"
Set pvf = pvt.PivotFields(strField)
With pvf
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0.00"
End With
Code: Select all
pvt.AddFields RowFields:=Array("Nominal", "Description"), ColumnFields:="Company"
My only thought of a workaround would be to concatenate the source data (nominal & description) into one, but I would rather not if there is a way to avoid the totalling.