VBA to create pivot table

[VegasNath]

Hi,
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

In the source data, each nominal has a corresponding description which I would like to show in the pivot alongside the nominal, so I adjusted the code as follows:

Code: Select all

    pvt.AddFields RowFields:=Array("Nominal", "Description"), ColumnFields:="Company"

However, a total row is now created below each nominal & description line which is not required as each total will only ever represent one line item. How can I remove the totals?

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.

[StuartR]

...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.

There goes the only suggestion that I had for you!

[VegasNath]

...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.

There goes the only suggestion that I had for you!

Blast! I feared as much. Thanks Stuart.

[VegasNath]

I found a way....
If you right click one of the subtotals, you can uncheck the subtotal option which collapses them. With the help of the macro recorder...... modified for my needs.

Code: Select all

    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, TableName:=wshSrc.Name & " Pivot")
    
    pvt.AddFields RowFields:=Array("Nominal", "Description"), ColumnFields:="Company"
    Set pvf = pvt.PivotFields(strField)
    With pvf
        .Orientation = xlDataField
        .Function = xlSum
        .NumberFormat = "#,##0.00"
    End With
    
'This hides the nominal subtotals
    ActiveSheet.PivotTables(wshSrc.Name & " Pivot").PivotFields("Nominal").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)

[StuartR]

Code: Select all

...
    ActiveSheet.PivotTables(wshSrc.Name & " Pivot").PivotFields("Nominal").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)

Thanks for the education