locked
Working of Grouping on Date field in pivot table depends on ... ?? RRS feed

  • Question

  • Hello,

    The following part of code does work :

    Sub Group_Year_Quarter()

    Dim df As PivotField

    Set df = Sheets("Nieuwe DF").PivotTables("Nieuwe Dealflow").PivotFields("Date received")

    df.LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, False, True, True)

    End Sub

    The same code applied on another sheet and another pivot, but same field gives the error : Run time error '1004' : Group method of Range class failed.

    The only difference I see is that in the code that fails there is a pivot chart on top of the pivot table. When I manually update the grouping on the pivot that fails via VBA all subsequent pivot tables in my Excel are updated as well.

    The following code fails :

    Sub Group_Year_Quarter()

    Dim df As PivotField

    Set df = Sheets("Historische - Kapitaal - aantal").PivotTables("PivotTable1").PivotFields("Date received")

    df.LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, False, True, True)

    End Sub

    Monday, May 7, 2018 8:33 AM

All replies

  • Hello Jan_senior,

    >>When I manually update the grouping on the pivot that fails via VBA all subsequent pivot tables in my Excel are updated as well.

    Since you could manually update the group, I would suggest you record a macro of it and then try to check if there is any setting different with your code.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, May 8, 2018 5:12 AM