VBA and Pivot Tables: getting two data fields

Question

I'm trying to use vba to create a pivot table from a data from a sheet entitled DATA. Is organised in a number of columns, three of which are called NOM, NOM_MOVE and COMMENT receptively.

I want the pivot table to have the following field: Col labels field = Sum(Values), Row labels field = COMMENT, Values field = sum of NOM, sum of NOM_MOVE

However, the code I have written doesn't work or give the right fields (even in different forms using pivottablewizard and cache/createpivottable.

My code up until where I am having trouble assigning the right fields is:

Sub CreatePivot()
' Create the 5 summary pivot tables using the TRADE_LIST sheet as data
Dim pivot1 As Worksheet
Dim datasheet As Worksheet
Dim dataCache As PivotCache
Dim PVT As PivotTable
Dim LastRow As Long
Dim LastCol As Long
Dim dataSource As Range
Dim PVTdest As Range

Set datasheet = Sheets("DATA")
Set pivot1 = Sheets("PIVOT")

' (1) Source data from the TRADE_LIST sheet and create a pivot cache from source data                     to use for each pivot table

LastRow = datasheet.Cells(1, 1).End(xlDown).Row
LastCol = datasheet.Cells(1, 1).End(xlToRight).Column
Set dataSource = datasheet.Cells(1, 1).Resize(LastRow, LastCol)

Set dataCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=dataSource)

' (2) Create a pivot table
Set PVTdest = pivot1.Cells(2, 2)
Set PVT = dataCache.CreatePivotTable(tabledestination:=PVTdest,              tablename:="Comment_Sums")

...

Any help much appreciated

L42 L42 · Accepted Answer · 2013-10-25T09:31:12

I made some revisions to your code so try this:

Sub CreatePivot()
' Create the 5 summary pivot tables using the TRADE_LIST sheet as data
Dim LastRow, LastCol As Long
Dim dataSource, destination As String
Dim wb As Workbook
Dim data_sheet, pivot1 As Worksheet

Set wb = ThisWorkbook
Set data_sheet = wb.Sheets("DATA") 'in your code, you forgot to include WB
Set pivot1 = wb.Sheets("PIVOT")

' (1) Source data from the TRADE_LIST sheet and create a pivot cache from source data _
  to use for each pivot table

'i changed this part to cover all data 
LastRow = data_sheet.Cells(data_sheet.Rows.Count, 1).End(xlUp).Row
LastCol = data_sheet.Cells(1, data_sheet.Columns.Count).End(xlToLeft).Column

'PivotCache creation requires arguments in string format, no need to use Set
dataSource = data_sheet.Name & "!" & data_sheet.Cells(1, 1).Resize(LastRow, LastCol).Address(ReferenceStyle:=xlR1C1)
destination = pivot1.Name & "!" & pivot1.Range("A1").Address(ReferenceStyle:=xlR1C1)

'here we use the datasource and destination
wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    dataSource, Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:=destination, TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion12

'after creating pivot, add the Row Field
With pivot1.PivotTables("PivotTable1").PivotFields("Comment")
    .Orientation = xlRowField
    .Position = 1
End With

'then add the column fields
With pivot1
    .PivotTables("PivotTable1").AddDataField .PivotTables( _
        "PivotTable1").PivotFields("NOM"), "Sum of NOM", xlSum
    .PivotTables("PivotTable1").AddDataField .PivotTables( _
        "PivotTable1").PivotFields("NOM_MOVE"), "Sum of NOM_MOVE", xlSum
End With

End Sub

Hope this get's you started.

VBA and Pivot Tables: getting two data fields

1 Answers