Group by Month in Pivot Table VBA Excel 2010

[nstratton]

I understand the title says it is for Excel but bear with me because the code is being run through Access.

I am exporting data to Excel to generate charts. I am about 98% done with this task but I am missing one chart that I am trying to base off a pivot because I do not know a better way to do it.

I can create the pivot fine and place the fields where I want them, I am just struggling to group the dates by month. I have browsed online and tried all the variations I have come across but none of them work. The code I am (attempting) to use is below. Any help will be infinitely appreciated.

    Dim groupRange As Range
    Set objTable = ws.PivotTableWizard
    Set objField = objTable.PivotFields("Date")
    Set groupRange = objField.DataRange
    groupRange.Cells(2, 1).Group Periods:=Array(False, False, False, False, True, False, False)
    objField.Orientation = xlColumnField

 

[sxschech]

Could you export the data using a crosstab query so that it is already sorted and grouped the way you wanted?

 

[nstratton]

I did not think about that. I do not think I can do that since the data only has to be sorted for this one chart. Basically what is happening now is I am taking a query and copying the recordset to a sheet in Excel and then I am applying all these filters and what not to create the other charts.

The one I am struggling with is creating a Top 5 by Month type of chart. The best way I can think of is through Pivot Table/Chart creation. It just doesn't make sense why the code is not working within Access. I have copied that code directly from Excel Macro and everything online matches up with what I have above. Unless anyone knows of a better way then I will keep trying different things until it works.

 

[nstratton]

I appreciate your help. The issue was the order in which I was trying to make everything run. I believe I was trying to group before data was placed into the Pivot Table