SOLVED

Pivot table cell names

%3CLINGO-SUB%20id%3D%22lingo-sub-2627754%22%20slang%3D%22en-US%22%3EPivot%20table%20cell%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2627754%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20I%20cannot%20rename%20the%20cells%20in%20a%20pivot%20table%20back%20to%20the%20original%20names!%3C%2FP%3E%3CP%3EIs%20there%20a%20method%20(sheet%20method%20or%20in%20VBA)%20that%20allows%20me%20to%20do%20that%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3EI%20just%20can't%20imagine%20that%20MS%20did%20not%20predict%20that%20kind%20of%20problem%2C%20so%20there%20must%20be%20a%20solution%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2627754%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2628031%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20cell%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2628031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124058%22%20target%3D%22_blank%22%3E%40Jakub99%3C%2FA%3E%26nbsp%3BThe%20method%20to%20fix%20this%20depends%20on%20the%20type%20of%20pivot%20table.%3C%2FP%3E%0A%3CP%3E1.%20Data%20model%20pivot%20tables%3A%3C%2FP%3E%0A%3CP%3ERecord%20a%20macro%20while%20filtering%20your%20field%20for%20just%20the%20renamed%20item%2C%20you%20get%20VBA%20code%20which%20will%20reveal%20the%20original%20item%20name.%20The%20recorded%20code%20will%20look%20similar%20to%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Macro1()%0A'%0A'%20Macro1%20Macro%0A'%0A%0A'%0A%20%20%20%20ActiveSheet.PivotTables(%22PivotTable1%22).PivotFields(%22%5BRange%5D.%5Bb%5D.%5Bb%5D%22).%20_%0A%20%20%20%20%20%20%20%20VisibleItemsList%20%3D%20Array(%22%5BRange%5D.%5Bb%5D.%26amp%3B%5Be%5D%22)%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThe%20item%20name%20is%20the%20last%20bit%20between%20square%20brackets%3A%20%5B%3CSTRONG%3Ee%3C%2FSTRONG%3E%5D%2C%20so%20you%20can%20use%20that%20to%20rename%20the%20pivot%20item%20back.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20pivottable%20is%20a%20regular%20(old-style)%20one%2C%20you%20must%20write%20some%20code%20yourself.%20The%20code%20below%20restores%20the%20name%20of%20a%20pivotitem%20of%20pivot%20field%20%22b%22%20in%20pivottable%20%22PivotTable2%22%20which%20was%20renamed%20to%20%22wfewfeferf%22%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Macro2()%0A'%0A'%20Macro2%20Macro%0A'%0A%0A'%0A%20%20%20%20With%20ActiveSheet.PivotTables(%22PivotTable2%22).PivotFields(%22b%22).PivotItems(%22wfewfeferf%22)%0A%20%20%20%20%20%20%20%20.Caption%20%3D%20.SourceName%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello everyone. 

My problem is that I cannot rename the cells in a pivot table back to the original names!

Is there a method (sheet method or in VBA) that allows me to do that? 

Thank you for your help.

I just can't imagine that MS did not predict that kind of problem, so there must be a solution

 

1 Reply
best response confirmed by Jakub99 (Occasional Visitor)
Solution

@Jakub99 The method to fix this depends on the type of pivot table.

1. Data model pivot tables:

Record a macro while filtering your field for just the renamed item, you get VBA code which will reveal the original item name. The recorded code will look similar to this:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Range].[b].[b]"). _
        VisibleItemsList = Array("[Range].[b].&[e]")
End Sub

The item name is the last bit between square brackets: [e], so you can use that to rename the pivot item back.

 

If your pivottable is a regular (old-style) one, you must write some code yourself. The code below restores the name of a pivotitem of pivot field "b" in pivottable "PivotTable2" which was renamed to "wfewfeferf":

 

Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("b").PivotItems("wfewfeferf")
        .Caption = .SourceName
    End With
End Sub