PIVOT TABLE HELP!

[sll810]

Sub GroupPivotDates(InPivTable As Excel.PivotTable, strMonthOrQuarter As String)

Dim xlPivDateField As Excel.PivotField, xlGroupRange As Excel.Range

Set xlPivDateField = InPivTable.PivotFields("Dates")
Set xlGroupRange = xlPivDateField.DataRange
If strMonthOrQuarter = "Month" Then
xlGroupRange.Cells(1).Group Periods:= _
Array(False, False, False, False, True, False, True)
Else
xlGroupRange.Cells(1).Group Periods:= _
Array(False, False, False, False, False, True, True)
End If


Set xlPivDateField = Nothing
Set xlGroupRange = Nothing
End Sub

 

[sll810]

Sorry the whole message didn't copy

I am creating a pivot table from a query in access. I am able to bring in all the data OK and group it by date. The problem is I want the average for each month once it is grouped by month and I am getting the sum of each month at the current moment. My call to the sub procedures is below:

'Produce Pivot table for avg daily information for shares and executions
Call ProducePivTable(PivTable, avgdailysheet, prange1, "SharesExecuted")
With PivTable
    'Add Fields
    .PivotFields("tradedate").Orientation = xlRowField
    .PivotFields("tradedate").Caption = "Dates"
End With
Call GroupPivotDates(PivTable, "Month")

With PivTable
    .PivotFields("SumOfsharesexecuted").Orientation = xlDataField
    .PivotFields("SumOfsharesexecuted").Caption = "AvgSharesExecuted"
End With

PivTable.PivotFields("SumOfSharesexecuted").Function = xlAverage

The code for the produce pivot table is

Sub ProducePivTable(xlPivTable As Excel.PivotTable, xlDataSheet As Excel.Worksheet, _
                            xlTableDest As Excel.Range, tblename As String)


    Dim xlapp As Excel.Application, xlLastRow, xlLastRow2 As Excel.Range, xlPivotRng, xlPivotRng2 As Excel.Range, xlLastCell, xlLastCell2 As Excel.Range
    Dim LastCol As Integer, LastRow, LastRow2 As Long
       
    'Creates pivot tables
    LastRow = xlDataSheet.Range("A" & xlDataSheet.Rows.Count).End(xlUp).Row
    xlDataSheet.Range("A2:A" & LastRow).NumberFormat = "mm/dd/yyyy"
    
    Set xlLastCell = xlDataSheet.Cells(LastRow, 2)
    Set xlPivotRng = xlDataSheet.Range(xlDataSheet.Range("A1"), xlLastCell)
    Set xlPivTable = xlDataSheet.Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        xlPivotRng).CreatePivotTable(TableDestination:=xlTableDest, _
        tablename:=tblename, DefaultVersion:=xlPivotTableVersion10)
      
    Set xlPivotRng = Nothing
    Set xlLastRow = Nothing
    Set xlapp = Nothing
End Sub

and the code for group by date is

Sub GroupPivotDates(InPivTable As Excel.PivotTable, strMonthOrQuarter As String)

    Dim xlPivDateField As Excel.PivotField, xlGroupRange As Excel.Range
    
    Set xlPivDateField = InPivTable.PivotFields("Dates")
    Set xlGroupRange = xlPivDateField.DataRange
    If strMonthOrQuarter = "Month" Then
        xlGroupRange.Cells(1).Group Periods:= _
            Array(False, False, False, False, True, False, True)
    Else
        xlGroupRange.Cells(1).Group Periods:= _
            Array(False, False, False, False, False, True, True)
    End If

           
    Set xlPivDateField = Nothing
    Set xlGroupRange = Nothing
End Sub

I have tried to insert the average function in multiple locations and have still had no luck. I would appreciate ANY help!!!! Thanks

 

[sll810]

I have resolved the issue. You need to adddatafields not pivot fields.

With PivTable
.AddDataField .PivotFields("SumOfsharesexecuted"), "AvgSharesExecuted", xlAverage
end with