CODEWITHSUNDEEP
excelvba
Scott Davies
Scott Davies

Reputation: 103

Excel VBA to group by month and year for all pivot tables on activesheet

I have a sheet where users can select from a drop down list for a number of fields to use for the group by row label. When they choose a date field, I would like to group by month and year. But I am getting an error with the following code. The error is

Run-time error '1004': Group method of Range class failed

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sField As String, sFieldName As String
Dim oRF As Object
Dim pPT As PivotTable
Dim pPF As PivotField

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$F$3" Then
    Application.EnableEvents = False
    sField = Target.Value
    sFieldName = Target.Offset(0, 1).Value



    For Each pPT In ActiveSheet.PivotTables
        'Remove all pivot fields for all pivot tables in this sheet
        For Each oRF In pPT.RowFields
            Debug.Print oRF.Name
            oRF.Orientation = xlHidden
        Next

        'Add the new pivot field
        pPT.PivotFields(sField).Orientation = xlRowField
        pPT.PivotFields(sField).Position = 1   
        pPT.PivotCache.Refresh
        pPT.CompactLayoutRowHeader = sFieldName

        'If the pivot field is a date field, group by month and year
       If InStr(UCase(sFieldName), "DATE") > 0 Then
            'set range of dates to be grouped
            Set pPF = pPT.PivotFields(sField)

            'This next line causes the error
            pPT.PivotFields(sField).LabelRange.Group _
            Start:=True, End:=True, Periods:= _
               Array(False, False, False, False, True, True, True)

        End If
    Next

    Application.EnableEvents = True
End If
End Sub

Upvotes: 0

Views: 7757

Answers (2)

Scott Davies
Scott Davies

Reputation: 103

I managed to get it working using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sField As String, sFieldName As String
Dim oRF As Object
Dim pPT As PivotTable

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$F$3" Then
    Application.EnableEvents = False
    sField = Target.Value
    sFieldName = Target.Offset(0, 1).Value

    For Each pPT In ActiveSheet.PivotTables
        'Remove all pivot fields for all pivot tables in this sheet
        For Each oRF In pPT.RowFields
            Debug.Print oRF.Name
            oRF.Orientation = xlHidden
        Next

        'Add the new pivot field
         pPT.PivotFields(sField).Orientation = xlRowField
         pPT.PivotFields(sField).Position = 1
         pPT.PivotCache.Refresh
         pPT.CompactLayoutRowHeader = sFieldName

         'If the pivot field is a date field, group by month and year
         If InStr(UCase(sFieldName), "DATE") > 0 Then


            pPT.PivotFields(sField).DataRange.Select
            Selection.Group _
                Start:=True, End:=True, Periods:= _
                Array(False, False, False, False, True, False, True)
        End If
    Next
    ActiveSheet.AutoFilter.ApplyFilter
    Range(Target.Address).Select
    Application.EnableEvents = True
End If
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

First, you already Set your PivotField here:

Set pPF = pPT.PivotFields(sField)

So you can use it at the following line.

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

However, you need to also add the Cells reference, something like:

pPF.LabelRange.Group.Cells(2, 1) _
Start:=True, End:=True, Periods:= _
    Array(False, False, False, False, True, True, True)

Note: you might also need to check the By parameter.

Upvotes: 1

Related Questions