Pivot Table - Column Over Written There Must be a way to

[Arts]

Hi all

I had created a series of pivot tables and I had no idea the row section could be edited i/e over written. I can't seem to find a solution for the life of me to reset the data to it's original value, example below using simple data

we have the below pivot table

But someone had overwritten one of the row labels which I didn't think was possible, James Lobeon is now 568. I assumed I would get the message "We Can't change this part of the PviotTable"

But I cant seem to get it back to it's original value. I have tried refresh, refresh all and the value doesn't seem to change to its original state. I did find this quite strange as I assumed this would go back to the original source data once refreshed and remove the 568 as that value isn't in the original source data. I did find a way to clear the whole pivot table by going to "PivotTable Analyse", Clear> Clear All but this deletes all the information from the pivot table meaning you would have to rebuild it from scratch and if a large pivot table which in my case it is having to do this each time would be quite annoying.



As always any help on this is most appreciated.

Arts

 

[Arts]

Ah ok well you change change that. When protecting sheet allow Use Pivot Table and Edit Objects

Thanks Steve I'll try that! If you do stumble across a fix for the initial issue do let me know, is really bugging me!!

 

[Alex Blakenburg]

There are a couple of options for resetting it.
1) Manual method
Remove the Names field from the Row area of your pivot table
Refresh the pivot (essential step)
Then put the Names field back

It should now reset to the original name.

2) Use VBA
Here is some code initially posted on StackOverflow here:
Excel Pivot Table Row Labels Not Refreshing

Sub CleanCaption()

Dim sht As Worksheet
Dim pvt As PivotTable
Dim fld As PivotField
Dim itm As PivotItem

For Each sht In Worksheets
    For Each pvt In sht.PivotTables
        For Each fld In pvt.PivotFields
            If Not IsError(fld.Position) Then
                For Each itm In fld.PivotItems
                    If Not itm.Caption = itm.SourceNameStandard Then itm.Caption = itm.SourceNameStandard
                Next
            End If
        Next
    Next
Next
End Sub

 

[Arts]

There are a couple of options for resetting it.
1) Manual method
Remove the Names field from the Row area of your pivot table
Refresh the pivot (essential step)
Then put the Names field back

It should now reset to the original name.

2) Use VBA
Here is some code initially posted on StackOverflow here:
Excel Pivot Table Row Labels Not Refreshing

Sub CleanCaption()

Dim sht As Worksheet
Dim pvt As PivotTable
Dim fld As PivotField
Dim itm As PivotItem

For Each sht In Worksheets
    For Each pvt In sht.PivotTables
        For Each fld In pvt.PivotFields
            If Not IsError(fld.Position) Then
                For Each itm In fld.PivotItems
                    If Not itm.Caption = itm.SourceNameStandard Then itm.Caption = itm.SourceNameStandard
                Next
            End If
        Next
    Next
Next
End Sub

Hmm the manual method seems to work on my test file but not on the file on the shared server (the work file)...

 

[Alex Blakenburg]

Is your test file a copy of the one on the shared server or a made up file ?
Give the macro a try.

 

[Arts]

The test file is a made up file mirroring the same issue. I really am quite surprised this is actually a thing/issue on excel. I would give the macro a try but honestly people here have manged to "break" a pivot I can't imagine what would happen if they ever saw code.

I'm also not the most savvy at VBA one thing I wish I could get into but over the years given multiple tries and it just doesn't stick. I think the issue is I've learnt from several places and each person has a different way of teaching and then the macro recorder doesn't list the code in a way you're taught ie no dim this, option explicit etc but anyhow a topic for another time.

 

[Alex Blakenburg]

There are 2 separate issues.
1) How to fix the current incorrect labels
2) How to prevent it from happening in the future

We are focussing on item 1 at the moment.
The macro does not need to be in the workbook with the problem and I am sure the recommendation where I got it from was to put it in your personal macro workbook.
At this stage create a new workbook and in the VBA developer screen Insert a Module in the new workbook.
Paste the macro into the code window there.
Now in the spreadsheet view make sure the workbook you are trying to fix is the Active workbook.
Now run the code either from within the module or in spreadsheet mode type Alt+<F8> and select the macro and hit run.
The macro will run on the Active Workbook and go through ALL Sheets and ALL pivots and ALL Fields in that workbook.

 

[Arts]

There are 2 separate issues.
1) How to fix the current incorrect labels
2) How to prevent it from happening in the future

We are focussing on item 1 at the moment.
The macro does not need to be in the workbook with the problem and I am sure the recommendation where I got it from was to put it in your personal macro workbook.
At this stage create a new workbook and in the VBA developer screen Insert a Module in the new workbook.
Paste the macro into the code window there.
Now in the spreadsheet view make sure the workbook you are trying to fix is the Active workbook.
Now run the code either from within the module or in spreadsheet mode type Alt+<F8> and select the macro and hit run.
The macro will run on the Active Workbook and go through ALL Sheets and ALL pivots and ALL Fields in that workbook.

I will try and give this ago, am already kind of lost when reading it if I'm honest. I didn't know there are different places I could put the code ie workbook, personal macro workbook and a module??? I've got more questions now if anything

I'll read and try the steps one a time and give this ago but have a gut feeling this wont go as hoped.

Thank you for taking the time to respond to this.

 

[Alex Blakenburg]

I am about to log off for the night. It sounds like you have recorded a macro before.
So in a new workbook, just quickly record a macro. Just hit record. Put a number in a cell and hit stop.

That will create a module and its parent Modules in the new workbook for you.

In that same module copy that code.
Make the problem workbook active in spreadsheet view and run the macro.

 

[Arts]

I am about to log off for the night. It sounds like you have recorded a macro before.
So in a new workbook, just quickly record a macro. Just hit record. Put a number in a cell and hit stop.

That will create a module and its parent Modules in the new workbook for you.

In that same module copy that code.
Make the problem workbook active in spreadsheet view and run the macro.

View attachment 77626

I have flirted with it, thank you for the above showing me how to record/enter the macro I'll give this a whirl. This seems quite excessive for something that should be relatively straight forward .

Have a good evening, appreciate all your assistance.

 

[Arts]

I am about to log off for the night. It sounds like you have recorded a macro before.
So in a new workbook, just quickly record a macro. Just hit record. Put a number in a cell and hit stop.

That will create a module and its parent Modules in the new workbook for you.

In that same module copy that code.
Make the problem workbook active in spreadsheet view and run the macro.

View attachment 77626

Alex (for when you log on)

I have no idea why or how that worked but it did!! Thank you for the step by step guidance on this! (post 18 was gold!)

If anyone has a non macro way of fixing should this issue occur again that would be most helpful/appreciated!

Arts