Contextures

Excel Pivot Item Macros

Change Pivot Items in an Excel Pivot Table with these macros. Get the free workbook with the macro code.

Introduction

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.

Pivot Item Caption

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 againgo to top

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 Sample File

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. go to top

Get All the Excel News

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 Tutorials:

Clear Old Items in Pivot Table Drop Downs

Show/Hide Pivot Items

Pivot Table Introduction

Grouping Data

Pivot Table from Multiple Sheets

Running Totals

Summary Functions

Search Contextures Sites

 

Free Pivot Table Tools

 

 

Free Pivot Table Tools

 

 

Last updated: December 29, 2016 3:28 PM
Contextures RSS Feed