group all categories under 5% to "other" in piechart or pivot

[primprim]

Hi again and thanks for all the help so far!
On to my next problem.... I am creating a piechart out of a pivot table. I am using a makro to make the pivot table choose all used cells and the pie chart to source the pivot table.

I want the chart to show eg 3 categories and then group the rest in one group called "others".

Eg. lets say i have a pivot table showing total amount sold by a store. ie;
Store A $100000
Store B $80000
Store C $85000
Store D $2000
Store E $7000
Store F $500

Then I want the pie chart to show:
Store A $100000
Store B $80000
Store C $85000
OTHER $9500

Basically, I want the pivottable or chart to automatically sort all categories that are eg under 5% of total to be grouped into other. Is there a way to do this? either by using a makro or manually applying the grouping... or is there a way to apply the grouping directly to the pivot table?

thanks in advance!

 

[primprim]

managed to solve it... so if anyone else has the same problem, here is what i wrote:

Dim PT As PivotTable
Dim rngBig As Range, rngCell As Range
Dim other As Range
Dim ColumnTotal As Range


Set PT = ActiveSheet.PivotTables("PivotTable1")
Set rngCell = Nothing
Set rngBig = PT.PivotFields("group").DataRange 'trade date

With PT.RowRange
        Set ColumnTotal = .Rows(.Rows.Count).Offset(0, 1)
End With

On Error Resume Next
For Each rngCell In rngBig.Cells
    If rngCell.Offset(0, 1).Value / ColumnTotal < 0.2 Then
        If other Is Nothing Then
            Set other = rngCell
        Else
            Set other = Application.Union(other, rngCell)
        End If
    End If
Next rngCell
other.Group
PT.PivotFields("Group2").PivotItems("Group1").Caption = "other"
PT.PivotFields("Group2").PivotItems("other").ShowDetail = False