• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change Caption before making group in Pivot Table

AAP

Member
Hi,
In pivot table I want to make a group for the selected data. By default excel takes a name for eg. Group1
But if that code runs in a different language of excel then default name changes from Group1 to Gruppe1 or so on depending on the language of excel installed.

I want to control that default name.

Code: Range.Group

If anyone can help me here.

Kind Regards
AAP
 
You cannot control the caption that is automatically generated. You need to locate the correct item (usually by locating the parent item in the prior row/column field) and then change it.
 
It would be easier if you can upload sample. But at any rate, there is no way to set default Group naming convention as far as I know.

Here's one way to change Group names. Assuming you want to keep the numeric portion and just change "Group" to string of your choice.
Code:
Sub Macro1()
Dim pvt As PivotTable
Dim pvi As PivotItem
Dim i As Long
    Set pvt = ActiveSheet.PivotTables(1)
    For i = 1 To pvt.RowFields.Count
        For Each pvi In pvt.RowFields(i).PivotItems
            If InStr(pvi.Name, "Group") Then
                pvi.Name = Replace(pvi.Name, "Group", "MyBucketName")
            End If
        Next
    Next
   
End Sub
 
It would be easier if you can upload sample. But at any rate, there is no way to set default Group naming convention as far as I know.

Here's one way to change Group names. Assuming you want to keep the numeric portion and just change "Group" to string of your choice.
Code:
Sub Macro1()
Dim pvt As PivotTable
Dim pvi As PivotItem
Dim i As Long
    Set pvt = ActiveSheet.PivotTables(1)
    For i = 1 To pvt.RowFields.Count
        For Each pvi In pvt.RowFields(i).PivotItems
            If InStr(pvi.Name, "Group") Then
                pvi.Name = Replace(pvi.Name, "Group", "MyBucketName")
            End If
        Next
    Next
  
End Sub
It's good to see your suggestion but as I wrote "Group" the finding element can take any name based on language installed.
 
That's just sample code to show how to access specific field/item.

So, let's say that you group PivotTable's RowField named "Company".

Then Groups would be stored in newly created PivotTable RowField that has name of "Company2". You can then rename each item in that RowField.

Alternately, you can store count of RowFields in a variable before you group rows. Add 1 to that count. Then use that value to access the RowField storing groups by using index of RowFields.
 
That's just sample code to show how to access specific field/item.

So, let's say that you group PivotTable's RowField named "Company".

Then Groups would be stored in newly created PivotTable RowField that has name of "Company2". You can then rename each item in that RowField.

Alternately, you can store count of RowFields in a variable before you group rows. Add 1 to that count. Then use that value to access the RowField storing groups by using index of RowFields.

I have uploaded a file. Could you please suggest.
 

Attachments

  • Sample Book.xlsb
    85.7 KB · Views: 2
Something like below.
Code:
For i = 1 To pvt.RowFields.Count
    If pvt.RowFields(i).Name = "Name2" Then
        For Each pvi In pvt.RowFields(i).PivotItems
            With CreateObject("VBScript.RegExp")
                .Pattern = "[0-9]+$"
                If .Test(pvi.Name) Then
                    pvi.Name = "myGroupName" & .Execute(pvi.Name)(0)
                End If
            End With
        Next
    End If
Next

Edit: Depending on number of RowFields, and if you are grouping more than 1 rowfield. You'll need to modify code to accommodate for that. I'd probably check that RowField name end in numeric value or some other criteria.
 
A simple way is to check for the item in Name2 that doesn't exist in Name like this:

Code:
Sub Macro1()
Dim pvt As PivotTable
Dim pvi As PivotItem
Dim i As Long, Rng, r As Range, Cut As Integer

Cut = 2

Set pvt = ActiveSheet.PivotTables(1)
   
Set Rng = ActiveSheet.Range(Range("B3"), Range("B3").End(xlDown))

With Rng
    Set r = .Find(Cut, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
End With
r.Select

Range(r, r.End(xlDown)).Offset(0, -1).Group

For Each pvi In pvt.PivotFields("Name2").PivotItems
    On Error Resume Next
    Debug.Print pvt.PivotFields("Name").PivotItems(pvi.Caption).Caption
    If Err.Number <> 0 Then
        pvi.Caption = "IMPORTANT NAMES"
        Exit For
    End If
    On Error GoTo 0
Next pvi
   
End Sub
 
A simple way is to check for the item in Name2 that doesn't exist in Name like this:

Code:
Sub Macro1()
Dim pvt As PivotTable
Dim pvi As PivotItem
Dim i As Long, Rng, r As Range, Cut As Integer

Cut = 2

Set pvt = ActiveSheet.PivotTables(1)
  
Set Rng = ActiveSheet.Range(Range("B3"), Range("B3").End(xlDown))

With Rng
    Set r = .Find(Cut, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
End With
r.Select

Range(r, r.End(xlDown)).Offset(0, -1).Group

For Each pvi In pvt.PivotFields("Name2").PivotItems
    On Error Resume Next
    Debug.Print pvt.PivotFields("Name").PivotItems(pvi.Caption).Caption
    If Err.Number <> 0 Then
        pvi.Caption = "IMPORTANT NAMES"
        Exit For
    End If
    On Error GoTo 0
Next pvi
  
End Sub

This technique worked. Thanks
 
Something like below.
Code:
For i = 1 To pvt.RowFields.Count
    If pvt.RowFields(i).Name = "Name2" Then
        For Each pvi In pvt.RowFields(i).PivotItems
            With CreateObject("VBScript.RegExp")
                .Pattern = "[0-9]+$"
                If .Test(pvi.Name) Then
                    pvi.Name = "myGroupName" & .Execute(pvi.Name)(0)
                End If
            End With
        Next
    End If
Next

Edit: Depending on number of RowFields, and if you are grouping more than 1 rowfield. You'll need to modify code to accommodate for that. I'd probably check that RowField name end in numeric value or some other criteria.
This technique worked. Thanks dear
 
Back
Top