Change source's column Name in a workbook with many pivot tables

If they are all the same cell position on each sheet, then you can select all sheets from the sheet selection control in the bottom left. I.e. click on Sheet2, then press shift and click on Sheet6.

Then just edit the cell (B1 in your example) on the active sheet and it will change on all selected sheets. remember to then click on a single sheet again to unselect all the sheets.

If its in a different position on each sheet then this method will not work.

I don't understand your instructions at all - have you tried them in Excel 2010?

In Excel 2010, when I want to change a pivot table, I click on a cell inside of it. Immediately after I click, "PivotTable Tools" appears on the ribbon. That is the essential set of tools that allow me to modify the tables.

When selecting two sheets simultaneously the "PivotTable Tools" disappears, so it is impossible to use them to affect the pivot table.

And, any attempt to alter any of the pivot table cells results in the message "cannot edit a PivotTable in group edit mode"

Try it yourself,
Create a workbook with only Sheet 1.

In sheet1 put in data like is specified in my first post. Put the word "part" is in cell b1.

Use the pivot table wizard to create two tables with both havingPart as a row label.  

Excel will create Sheet2 and Sheet3  both of which will have the word "part" in cell A5.

Now go to sheet 1 and try to change the word Part to "Model".
When you refresh the pivot tables, they all get crushed.

I would imagine that a macro could automate it for you.  I don't work with pivot tables much so if you post a simple workbook, it would be easier to help.

I have attached a workbook that demonstrates some of the problems. That workbook caused me to add the following steps to my proposed answer,  None of the other steps are changed.


4B)  (optional) If the worksheet uses cells outside of the pivot table borders it is best to surround the table with several empty columns and empty rows. Otherwise you will receive annoying messages that ask:  “Do you want to replace the contents of the destination cells?”.

 
8B)  (optional)  If worksheet cells outside of the table borders reference cells inside the borders, those formulas may suddenly show #VALUE. Ignore this for a while, they may correct themselves at step 9.

8C)    If necessary, make the Model field consistent with the original Part field.  For instance if the old field was "Max of Part", you will need to do the following to "Model".
     
    Click "Model" dropdown arrow.
    click <Value Field Setting>
    Choose "Max" for the drop down list.
pivot-demo.xlsx

I am not sure what you are doing with all those steps.  

If you just want the label changed, in a Module, run this:

Sub PartToModelInAllPivotTables()
  Dim ws As Worksheet, pt As PivotTable
  On Error Resume Next
  For Each ws In Worksheets
    For Each pt In ws.PivotTables
      pt.PivotFields("part").Caption = "Model"
    Next pt
  Next
End Sub

Select allOpen in new window

It turns out to be difficult to rename a column of a pivot table's source.  None of the experts had any helpful suggestions, so I am accepting my own solution.

Apparently, you did not try my macro.

khobson:  


If you feel you can do a good job at a macro, give it a try and post your attempts.  If they look promising, I could open a new question and we can collaborate on improving it.


We could easily automate a few of the steps, but a general solutions is beyond my abilities.

Pivot tables have a huge number of options and variations most of which are influenced by the name of the source column.  

I've successfully tackled thousands of small macros, hundreds of large macros and dozens of huge macros, but this particular one seems unlikely to be worth the effort,

I already did an excellent job with the macro and posted it.  What it does is "Change source's column Name in a workbook with many pivot tables".

If there was a need to do something else other than change a column name, then it should be detailed in another question.

khobson: I now understand better.  See if you agree with the following.  If you do, I will have the moderator give you points.

LATEST "ACCEPTED ANSWER":



SIMPLE WAY TO FIX JUST SHEET2 TO SHEET5:

Changing sheet1 ! b1 from "Part" to "Model" is difficult because it is likely to damage your pivot tables.

Fortunately, changing sheet2 through sheet5 to show "Model" is easy and safe.  In each of those sheets, right click on the word "part" then choose field settings and change the custom name from part to Model.  

Or, you can use the following macro from KHobson.

Sub PartToModelInAllPivotTables()
  Dim ws As Worksheet, pt As PivotTable
  On Error Resume Next
  For Each ws In Worksheets
    For Each pt In ws.PivotTables
      pt.PivotFields("part").Caption = "Model"
    Next pt
  Next
End Sub

Select allOpen in new window

Please note, both the manual approach and the macro will refresh the table, so you might want to turn on the "Preserve Cell Formatting on Update" option.

SLIGHTLY MORE DIFFICULT WAY TO FIX SHEET1


To make sheet1 ! B1 show "Model" the simplest approach is to create a text box with the word "Model" in it, and fit that text box over Sheet1!b1.  

the cell's value is still "Part", but people looking at the spreadsheet will see "Model".


DIFFICULT WAY TO FIX SHEET 1, AND SHEET2 TO SHEET5.

The 10 steps outline in my third post above will do the trick, but be careful, they are likely to damage the pivot tables.

Points don't matter to me.  I just try to help people.

He is another snippet for fun.  I made it so each run will toggle it from "part" to "Model" and vice-versa. To test with multiple pivottables, I used your xlsx file and copied the sheet with the pivottable as I did for the first example code.

Sub PartToModelInAllPivotTables()
  Dim ws As Worksheet, pt As PivotTable
  Dim s As String, r As Range, s2 As String
  
  Set r = Worksheets("Sheet1").Range("B1")
  s = r.Value2
  Select Case s
    Case "part"
      s2 = "Model"
      r.Value2 = "Model"
    Case "Model"
      s2 = "part"
      r.Value2 = "part"
    Case Else
  End Select
  
  On Error Resume Next
  For Each ws In Worksheets
    For Each pt In ws.PivotTables
      pt.PivotFields(s).Caption = s2
    Next pt
  Next
End Sub

Select allOpen in new window

Since damage can be done, always test macro code on a backup copy of the workbook.

Well, if points don't matter, we will not bother the moderator.  And you most certainly did help me,  Thanks.

Bob