VBA group dates as month before adding columns to pivot chart

HFX_Martin

New Member
Joined
Nov 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
   ' 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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

severynm

Active Member
Joined
Jan 8, 2021
Messages
335
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,175,456
Messages
5,897,525
Members
434,659
Latest member
Fityi

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
Top