VBA group dates as month before adding columns to pivot chart

[HFX_Martin]

I have created a program that adds dates to the column of a pivot table and then groups the dates by year and month. Here is the section of code that performs this function:

   ' Add "EnteredOn dates field to pivot table column
    With ActiveSheet.PivotTables("Report_Usage").PivotFields("EnteredOn")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    'Group EnteredOn dates by years and months
    Dim rngGroup As Range
    Set rngGroup = ActiveSheet.PivotTables("Report_Usage").PivotFields("EnteredOn").DataRange
    rngGroup.Cells(1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)

I am running this code successfully against many workbooks, however I have run into one issue. Some of the workbooks have more than 16384 unique "Entered On" entries which exceeds the amount of columns allowed in a pivot chart. When the program initially tries to add the "Entered On" date, the program breaks. I need to group the dates by year/month before (or while) adding them to the pivot table to avoid breaking the column limit.

I would like to avoid using the .AutoGroup function. The data within each workbook can vary and 'AutoGroup causes inconsistent results.

Any thoughts?

Thanks!

 

[severynm]

If you are exceeding the limit for Pivot tables, I might suggest you look at doing the work with Power Query. You can use that to manipulate your data how you need and then filter it down somehow to send the results down to a pivot table.

Also related question - why are your "EnteredOn" fields columns?

 

[HFX_Martin]

Hi Severynm,

Thank you for the suggestion.

Power Query would definitely provide workarounds for this issue, however the ultimate plan for this project is for a large team of collogues to be able to run this program against their own client files. I am hoping to avoid Power Query if possible as I do not want this to be a requirement for all those folks.

I have found a workaround where if I first put the dates into the column, I can then group by month/year and then move it into the row. Not elegant or efficient by any means, so I am still looking for a more graceful solution.

Many thanks,
Martin