PIVOT TABLE HELP!

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:

Code:
'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
Code:
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
Code:
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
 
Upvote 0
I have resolved the issue. You need to adddatafields not pivot fields.

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

Forum statistics

Threads
1,216,381
Messages
6,130,297
Members
449,570
Latest member
TomMacca52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top