Multi level grouping in pivot tables

[a93299]

I've been working with this this for about 4 days so I thought I would share now that I solved it:

I got a pivottable (I construct from VBA)
Then I want to list data either in rows or groups of groups of groups (metagroups).
(in this case row would be a "verification/sale id", a group would be "web sales" and a metagroup would be "total sales")
-Total sales (metagroup)
--Web sales (group)
---sale 1 (sale)
---sale 2
---sale 3
--IRL sales
---sale a
--Space sales
--sale alpha
--sale gamma

So how to layout:
Problem 1: How to group rows
Solution: Make a string with a "Group query" with what row names I want to select.

Dim outputString as String
While(Not(IsEmpty(Cells(i,1))) 'while you still have non-empty rows
outputString = outputString + "'" + CStr(Cells(i,1).Value) + "',"
i = i +1
Wend
outputString = Left(outputString, Len(outputString) - 1) 'remove last comma

Problem 1: A Group with only one row cannot be grouped.
Solution: Rename row

Dim pivotName = "Name of your pivottable"
Dim pivotField = "Name of your pivotfield"
tempArray() = Split(outputString, ",")

If (UBound(tempArray()) = 0) Then 'if only one sale/id
                    outputString = Left(outputString, Len(outputString) - 1) 'remove first '
                    outputString = Right(outputString, Len(outputString) - 1) 'remove last ,

ActiveSheet.PivotTables(pivotName).PivotFields(pivotField).PivotItems(outputString).caption = "The text you want"

Else 'problem 2

Problem 2: When you make a group of sales you create a new PivotField
Solution: Count number of groups, do pivot selection, and group

'For output string, see below
numberOfGroups = 0
If (numberOfGroups = 0) Then
                    ActiveSheet.PivotTables(pivotName).PivotSelect pivotField & "[" + outputString + "]", xlDataAndLabel + xlFirstRow, True
                Else
                    ActiveSheet.PivotTables(pivotName).PivotSelect pivotField & numberOfGroups & "[" + outputString + "]", xlDataAndLabel + xlFirstRow, True
                End If
                Selection.Group
                                ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & numberOfGroups).PivotItems("Group" & (numberOfGroups + 1)).caption = "Your text here"
                ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & numberOfGroups).PivotItems(initCellPivot.Offset(2 + i, -2).Value).ShowDetail = False 'collapse
                numberOfGroups = numberOfGroups + 1
End if 'problem 2

Problem 3: Grouping groups
Solution: Here I got sort of lazy and didn't do this awsome function who handles both normal and metagroups and is dymanic, I might do that later. But this one does the job:

numberOfMetaGroups = 0

If (UBound(tempArray()) = 0) Then 'if only one account
                    outputString = Left(outputString, Len(outputString) - 1)
                    outputString = Right(outputString, Len(outputString) - 1)
                    
                    If (numberOfGroups= 0) Then
                        If (numberOfMetaGroups = 0) Then 'ifall groupLevel2 not exists
                            ActiveSheet.PivotTables(pivotName).PivotFields(pivotField).PivotItems(outputString).caption = "text"
                        Else
                 ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "2").PivotItems(outputString).caption = "text
                        End If
                    Else
                        If (numberOfMetaGroups = 0) Then 'no groupLevel3 exists
                            ActiveSheet.PivotTables(pivotName).PivotFields(pivotField).PivotItems(outputString).caption = "text"
                        Else
                            ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "2").PivotItems(outputString).caption = "text"
                        End If
                    End If
                Else
                    
                    If (numberOfGroups = 0) Then
                        If (numberOfMetaGroups = 0) Then
                            ActiveSheet.PivotTables(pivotName).PivotSelect pivotField & "[" + outputString + "]", xlDataAndLabel + xlFirstRow, True
                        Else
                            ActiveSheet.PivotTables(pivotName).PivotSelect pivotField & "2[" + outputString + "]", xlDataAndLabel + xlFirstRow, True
                        End If
                        Selection.Group
                        ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "2").PivotItems("Group" & (numberOfMetaGroups + 1)).caption = "Groupname"
                        ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "2").PivotItems("Groupname").ShowDetail = False
                        numberOfMetaGroups  = numberOfMetaGroups  + 1
                    Else
                        If (numberOfMetaGroups  = 0) Then
                            ActiveSheet.PivotTables(PivotName).PivotSelect pivotField & "2[" + outputString + "]", xlDataAndLabel + xlFirstRow, True
                        Else
                            ActiveSheet.PivotTables(pivotName).PivotSelect pivotField & "3[" + outputString + "]", xlDataAndLabel + xlFirstRow, True
                        End If
                        Selection.Group
                        
                        'to handle grouping of level 1 sales/id that results in a level2 group when you are acctually grouping on level3
                        Call nameGroup(pivotField, outputString, "groupName2", numberOfGroups, numberOfMetaGroups, pivotName)
                        End If
                End If

Function nameGroup(pivotField As String, outputString As String, caption As String, ByRef antalGrupper As Integer, ByRef antalMetaGrupper As Integer, pivotName as String)
'try catch as good as it gets
'some swedish variable names, but aslong as they are enclosed in this function you shouldn't have to worry
Dim i As Integer
Dim errorCounter As Integer
errorCounter = 0
On Error GoTo goBackOnePivotLevel
    ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "3").PivotItems("Group" & (antalMetaGrupper + 1)).caption = caption
    ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "3").PivotItems(caption).ShowDetail = False
    antalMetaGrupper = antalMetaGrupper + 1
    GoTo Done:
Try2:
    ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "2").PivotItems("Group" & (antalGrupper + 1)).caption = caption
    ActiveSheet.PivotTables(pivotName).PivotFields(pivotField & "2").PivotItems(caption).ShowDetail = False
    antalGrupper = antalGrupper + 1
    GoTo Done:
Done:
    errorCounter = 0
Exit Function

goBackOnePivotLevel:
errorCounter = errorCounter + 1
    If (errorCounter = 1) Then
        Resume Try2
    End If
Resume Done
End Function

I'll update this with a better metagroup-function when it's done