How can I copy the grouping rules from one pivot table to another pivot table?

[jaza_tom]

Hey long time fan, first time poster.

I understand how to edit a PivotTable's grouping:

• get a handle to the range object within the pivot table corresponding to the pivot field you want to group
• use that range object's Group method to group that pivotfield
  • there's a good tutorial for this here

What I can't figure out how to do is copy the grouping of a certain pivot table to another pivot table.

For example, if I have one pivot table who's RowFields are grouped like:

Range.Group Start:=0, End:=200, By:=10

then how can I programmatically:

• detect if a target pivot table's RowFields are grouped or not
• copy or parse/save the grouping rules of a target pivot table's RowFields
• apply those grouping rules to the RowFields of a second separate pivot table

the best I've been able to think up is to try to loop through the pivot items of a target pivot table that is grouped and use some type of pattern matching algorithm to determine what the start/end/interval values for the grouping should be...

'Function that copies the grouping of the pivotitem in the
'srcPivot previously selected in setPivotTarget() based on the user's selection in the active workbook's "pivot_copypaste_combobox" combobox (xlRowField, xlColumnField, xlPageField)
Public Sub pivotPasteGrouping()
    If ActiveChart Is Nothing Then Exit Sub
    If ActiveChart.PivotLayout.PivotTable Is Nothing Then Exit Sub
    If srcPivot Is Nothing Then Exit Sub
    Dim pasteType As XlPivotFieldOrientation
    ' Get the user's selection for pivot field type from the combo box
    pasteType = xlRowField
    Dim srcFields As PivotFields
    Set srcFields = srcPivot.RowFields
    Dim startVal As Integer
    Dim endVal As Integer
    Dim intervalVal As Integer
    'loop through the PivotItems of srcFields
    'and use a  pattern matching algorithm to determine
    ' what the start/end/interval values for the grouping should be
    'assuming that the pivotfields are numeric but grouped like:
    ' "<N" or "N" or "N-N" or ">N"
    'get handle to the srcField wih the highest position
    Dim srcField As PivotField
    For Each srcField In srcFields
        If srcField.Position = srcFields.Count Then Exit For
    Next srcField
    For Each srcItem In srcField.DataRange.Cells
        Dim itemVal As Double
        itemVal = FirstNumber(srcItem.Value)
        If InStr(srcItem.Value, ">") > 0 Then
            If itemVal > startVal Then endVal = itemVal
        ElseIf InStr(srcItem.Value, "<") > 0 Then
            If itemVal < endVal Then startVal = itemVal
        ElseIf InStr(srcItem.Value, "-") > 0 Then
            Dim splitVals As Variant
            splitVals = Split(srcItem.Value, "-")
            If UBound(splitVals) = 1 Then
                intervalVal = splitVals(1) - splitVals(0)
            End If
            If splitVals(0) < startVal Then
                startVal = splitVals(0)
            End If
        Else
            If itemVal > endVal Then endVal = itemVal
            If itemVal < startVal Then startVal = itemVal
        End If
    Next srcItem


    'find handle to the highest position pivotfield in the target pivot table targetfields
    'and group that field based on the start/end/interval values
    Dim targetFields As PivotFields
    Select Case pasteType
        Case xlRowField
            Set targetFields = ActiveChart.PivotLayout.PivotTable.RowFields
        Case xlColumnField
            Set targetFields = ActiveChart.PivotLayout.PivotTable.ColumnFields
        Case xlPageField
            Set targetFields = ActiveChart.PivotLayout.PivotTable.PageFields
    End Select
    For Each srcField In targetFields
        If srcField.Position = targetFields.Count Then Exit For
    Next srcField
    srcField.DataRange.Cells(targetFields.Count).Group Start:=startVal, End:=endVal, By:=intervalVal

End Sub

Any better ideas?