Open Forum

Excel: Typing over a pivot table row label

  • 1.  Excel: Typing over a pivot table row label

    SILVER CONTRIBUTOR
    Posted Apr 10, 2019 06:56 PM
      |   view attached
    Reaching out to the Excel gurus...

    We have noticed when typing over a pivot table row label, potentially by accident, the row label does not return upon refresh.  We need to keep the pivot table unprotected.  Any ideas?

    Example is attached, with fictional data.

    Thanks!
    Cathy

    ------------------------------
    Cathy McCarter
    Financial Analyst
    Innovative Wireless Technologies
    Lynchburg VA
    ------------------------------

    Attachment(s)



  • 2.  RE: Excel: Typing over a pivot table row label

    SILVER CONTRIBUTOR
    Posted Apr 10, 2019 07:06 PM
    The attachment was not loading; here is a picture:
    example

    ------------------------------
    Cathy McCarter
    Financial Analyst
    Innovative Wireless Technologies
    Lynchburg VA
    ------------------------------



  • 3.  RE: Excel: Typing over a pivot table row label

    Posted Apr 12, 2019 09:46 AM
    Hi Cathy,

    This macro will restore all original source labels to the pivot table.

    Sub FixPivotItemCaptions()
    Dim pi As PivotItem
    Dim pt As PivotTable
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pf As PivotField

    Set wb = ActiveWorkbook

    Application.ScreenUpdating = False
    For Each ws In wb.Worksheets
    For Each pt In ws.PivotTables
    For Each pf In pt.PivotFields
    For Each pi In pf.PivotItems
    pi.Caption = pi.SourceName
    Next
    Next
    Next
    Next
    Application.ScreenUpdating = True
    End Sub


    ------------------------------
    Crystal Polege
    Chapters Health System, Inc.
    Temple Terrace FL
    ------------------------------



  • 4.  RE: Excel: Typing over a pivot table row label

    SILVER CONTRIBUTOR
    Posted Apr 14, 2019 03:22 PM

    Thank you Crystal!  I have not tried it yet, but will do when I return from conference.  Thanks again for your time!

    Cathy



    ------------------------------
    Cathy McCarter
    Financial Analyst
    Innovative Wireless Technologies
    Lynchburg VA
    ------------------------------