Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find the original field name for a changed PivotField

PivotTable Tips

Find the original field name for a changed PivotField

by  SkipVought  Posted    (Edited  )
You have a PivotTable with a Source Data Table, either in another sheet or data from an external source, and you have changed one of the Field Names in the PivotTable, perfectly acceptable.

Now, you want to identify the Source Data Field Name for that Field Name you changed, and there seems to be no way to do that in Excel.

Paste this little function into a MODULE in the Visual Basic Editor. [tt]
How to:
1. alt+F11 (toggles between sheet and VB Editor)
2. ctr+R (displays the Project Explorer)
3. Insert > Module
[/tt]
Use as you would any spreadsheet function, on the sheet containing the PivotTable. Insert the function in a cell and reference the cell in the PivotTable with your changed Filed Name.
Code: 
Function PivotFieldSouceName(PvtFieldName As Range)
'SkipVought 2008/11/12
    Dim pvt As PivotTable, PvtTableNbr As Integer
'first find the PivotTable
    PvtTableNbr = 1
    For Each pvt In ActiveSheet.PivotTables
        If Not Intersect(pvt.TableRange1, PvtFieldName) Is Nothing Then Exit For
        PvtTableNbr = PvtTableNbr + 1
    Next
'return the value
    PivotFieldSouceName = ActiveSheet.PivotTables(PvtTableNbr).PivotFields(PvtFieldName.Value).SourceName
End Function
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top