Forum Discussion

Jakub99's avatar
Jakub99
Copper Contributor
Aug 09, 2021
Solved

Pivot table cell names

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 😄

 

excel
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Aug 09, 2021

    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

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Aug 09, 2021

    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

Resources