Date grouping on pivot table thru macros

[Costas]

Hi Chandoo,

I have a workbook with pivot tables and wrote a short macro that updates all pivots in all sheets.
In one of my pivot tables I have dates as rows and I am grouping them by month & year. After the update through my vba, the grouping is gone. I've tried to record my actions and apply them but it doesn't work. Below is my code:

    Dim Sh As Worksheet
    Dim Pt As PivotTable
    X = 0
    For Each Sh In ActiveWorkbook.Sheets
        For Each Pt In Sh.PivotTables
            Pt.PivotCache.Refresh
            'Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
            X = X + 1
        Next Pt
    Next Sh

I replaced selection with pt but it still does not work.

Any ideas?

 

[NARAYANK991]

Hi Costas ,

The GROUP method is not applicable to pivot tables ; however , it is applicable to a Range. The Excel help on the Range.Group method says this :

When the Range object represents a single cell in a PivotTable field’s data range, the Group method performs numeric or date-based grouping in that field.

The Range object must be a single cell in the PivotTable field’s data range. If you attempt to apply this method to more than one cell, it will fail (without displaying an error message).

Narayan