PDA

View Full Version : [SOLVED] Group pivot items



joshcas
05-31-2013, 12:57 PM
Hi,

I was wondering how can I group pivot items based on the name?, e.g. I want to group all the pivot items that are not named US or CA or BR , I did a little research and I found a code that I'm trying to adapt but I'm having some trouble to group the pivot items .

Any help will be much appreciated


With ActiveSheet.PivotTables("PivotTable5").PivotFields("Ship_To_Country")

For i = 1 To .PivotItems.Count

If .PivotItems(i).Name = "US" Or .PivotItems(i).Name = "CA" Or .PivotItems(i).Name = "BR" Or .PivotItems(i).Name = "MX" Then
' .PivotItems(i).LabelRange.Select

' cell1 = ActiveCell.Address
'MsgBox ("Default Regions")
Else

' Group the pivotitem


End If
Next i

' Union(cell1, cell2).Group

End With

p45cal
06-01-2013, 07:53 AM
this might work:Dim unionrng As Range
For Each itm In ActiveSheet.PivotTables("PivotTable5").PivotFields("Ship_To_Country").PivotItems
If Not (itm.Name = "US" Or itm.Name = "CA" Or itm.Name = "BR" Or itm.Name = "MX") Then
If unionrng Is Nothing Then Set unionrng = itm.LabelRange Else Set unionrng = Union(unionrng, itm.LabelRange)
End If
Next itm
unionrng.Group

joshcas
06-01-2013, 11:07 AM
Hey p45cal , thank you so much for taking the time to answer my inquiry.

I think We are getting close but I'm getting an error (“Unable to get the LabelRange Property of the PivotItem class”) on this part:

Set unionrng = Union(unionrng, itm.LabelRange)

Thank you again , happy Saturday

p45cal
06-01-2013, 11:27 AM
I think I'd need to see a version of your workbook where the error still occurs to work this one out. Attach a workbook here.

Paul_Hossler
06-01-2013, 03:10 PM
I added the Dim to p45cal's example, and this works, or at least doesn't throw an error


Sub sub1()
Dim itm As PivotItem ' < added
Dim unionrng As Range

For Each itm In ActiveSheet.PivotTables("PivotTable5").PivotFields("Ship_To_Country").PivotItems
If Not (itm.Name = "US" Or itm.Name = "CA" Or itm.Name = "BR" Or itm.Name = "MX") Then
If unionrng Is Nothing Then
Set unionrng = itm.LabelRange
Else
Set unionrng = Union(unionrng, itm.LabelRange)
End If
End If
Next itm

unionrng.Group
End Sub


Paul

Aflatoon
06-03-2013, 04:26 AM
You'd get that error if the item didn't have any data displayed (e.g. because of a filter applied). To avoid that:
Sub sub1()
Dim itm As PivotItem ' < added
Dim unionrng As Range
Dim rLabel As Range

For Each itm In ActiveSheet.PivotTables("PivotTable5").PivotFields("Ship_To_Country").PivotItems
If Not (itm.Name = "US" Or itm.Name = "CA" Or itm.Name = "BR" Or itm.Name = "MX") Then
On Error Resume Next
Set rLabel = itm.LabelRange
On Error GoTo 0
If Not rLabel Is Nothing Then
If unionrng Is Nothing Then
Set unionrng = rLabel
Else
Set unionrng = Union(unionrng, rLabel)
End If
End If
End If
Next itm

unionrng.Group
End Sub

joshcas
06-03-2013, 07:06 AM
Wow, brilliant solution , thank you so much p45cal, Paul_Hossler , Aflatoon . Hats off to you guys