none
VBA for Excel: Pivot tables: How do I change the Source Name of a Value?

    Question

  • I would like to programmatically change the Source Name for a data field Value in a Pivot table. Is this possible?

    I can't find a method or property that changes it on the DataField object.

    Tuesday, November 06, 2012 8:57 PM

Answers

  • I would like to programmatically remove a field from the Values list, but I can't find a Remove, RemoveField, or Delete method on the DataFields object.

    Is there some other way to do it?

    It's tricky and totally non-obvious. Drove me crazy till someone showed me the way.

    Public Sub PivotDataFieldRemove(ByRef poPT As PivotTable, ByVal pszRemovefield As String)
    
      Dim oPTfld As PivotField
      
      If PivotDatafieldExists(poPT, pszRemovefield) Then
        Set oPTfld = poPT.DataFields(pszRemovefield)
        With oPTfld
          .Parent.PivotItems(.Name).Visible = False
        End With
      End If
    
    End Sub

    • Marked as answer by AFCErik Tuesday, November 13, 2012 12:47 AM
    Wednesday, November 07, 2012 12:32 AM

All replies

  • I would like to programmatically remove a field from the Values list, but I can't find a Remove, RemoveField, or Delete method on the DataFields object.

    Is there some other way to do it?

    Tuesday, November 06, 2012 9:00 PM
  • I would like to programmatically remove a field from the Values list, but I can't find a Remove, RemoveField, or Delete method on the DataFields object.

    Is there some other way to do it?

    It's tricky and totally non-obvious. Drove me crazy till someone showed me the way.

    Public Sub PivotDataFieldRemove(ByRef poPT As PivotTable, ByVal pszRemovefield As String)
    
      Dim oPTfld As PivotField
      
      If PivotDatafieldExists(poPT, pszRemovefield) Then
        Set oPTfld = poPT.DataFields(pszRemovefield)
        With oPTfld
          .Parent.PivotItems(.Name).Visible = False
        End With
      End If
    
    End Sub

    • Marked as answer by AFCErik Tuesday, November 13, 2012 12:47 AM
    Wednesday, November 07, 2012 12:32 AM
  • I would like to programmatically change the Source Name for a data field Value in a Pivot table. Is this possible?

    I can't find a method or property that changes it on the DataField object.


    Just reference it as a PivotField object. From there you can change the Caption and/or the Name property.
    • Proposed as answer by Syswizard Saturday, November 10, 2012 1:59 AM
    Wednesday, November 07, 2012 12:34 AM