Change Pivot Items in an Excel Pivot Table with these macros. Get the free workbook with the macro code.
In a pivot table, when you add pivot fields, the individual pivot items in that field are also displayed.
Sometimes, old items appear, that are no longer in the data. Those can be removed by following the instructions and sample code here.
You can also show or hide specific items, either manually or programatically, and there are instructions and sample code here.
If pivot item labels have been changed, there is sample code below, to reset them back to the original source name.
In a pivot table, you can type over a label (caption), to replace the original text from the source data (source name), with new text. If one label is typed over, all the pivot table labels for that item are changed to the same text. This can be a problem, especially if the change was accidental, and you notice the change weeks later.
If a Pivot Field label is changed, you can see use the Field Settings dialog box to see its source name, and change it back to that text.
A similar feature isn't available for Pivot Items, but you can use programming to reset the caption. With the following macro, you can select a pivot item label, and reset the caption, so it shows the source name again
SubFixPivotItemCaption() Dim pi As PivotItem Dim lRsp As Long Dim strCap As String Dim strSN As String On Error Resume Next 'Set pt = ActiveCell.PivotTable Set pi = ActiveCell.PivotItem If pi Is Nothing Then MsgBox "Please select a pivot item label cell" Else strCap = pi.Caption strSN = pi.SourceName lRsp = MsgBox("Reset this pivot item caption " _ & "to the source name?" _ & vbCrLf _ & "Source Name: " & strSN _ & vbCrLf _ & "Caption: " & strCap, _ vbQuestion + vbYesNo, "Reset Caption") If lRsp = vbYes Then pi.Caption = pi.SourceName End If End If End Sub
Download the zipped sample file to reset pivot item captions. The file is in xlsm format, and contains macros. When you open the file, enable macros, if you want to test the code.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
More Pivot Table Links
Last updated: December 29, 2016 3:28 PM
Contextures RSS Feed