pivot table custom name resets (excel 2007)

**bkj123** · 03-24-2011, 12:54 PM

Good day.

I'm having trouble preserving a custom name in an Excel 2007 pivot table. Below is the scenario. Thank you.

I have a pivot table in excel 2007 with:
- Column Labels: Months (jan, feb, etc.)
- Row Labels: none
- Values: count of active_cnt; count of base_cnt

I left mouse click on "count of active_cnt", and click "Value Field Settings..." where I change the custom name to "Active Count". I hit OK and the pivot table looks great.

I drag the "Active Count" column off of the pivot table since I did not want it. Later, I drag it back onto the pivot. The custom name returns to "count of active_cnt". Is there a way to change the custom name to "Active Count" and keep it that way?

**MarvinP** · 03-25-2011, 08:39 AM

Hi bkj123 and welcome to the forum,

I understand your question but but know know for sure how to solve it. Look at
http://www.informit.com/articles/article.aspx?p=688532
down at the bottom in a section of "Changing a Field Name" by putting a space on the end of it.

I hope this helps but you need to test it.

**bkj123** · 03-25-2011, 08:48 AM

Thank you Marvin for the reply.

The actual custom name value doesn't seem to be an issue as much as whatever I name it resets to the original if it is dragged off and onto the pivot. Short of VBA code, I'm wondering if this is even possible.

Thank you again

**MarvinP** · 03-25-2011, 11:29 AM

If you drag it off the pivot and back on the list, the name reverts back to the column heading. When you drag it back onto the list it is given the prefix of the operation. Like (Count, Sum, Min, Max...)
So two things are working here: Name of column head and operation.

I believe you would have to do VBA to change it.
I just recorded a macro to change the heading of one of my pivots from "Min of Ch Tank In" to "Min In" and got the following VBA code. Perhaps you can write a little routine to do what you need.

Please Login or Register  to view this content.

So the thing we are changing in called the DataPivotField.PivotItems(.....).Caption.

hope this gets you closer to your need.

**bkj123** · 03-25-2011, 12:06 PM

Thank you for the info and code Marvin. I replicated what you did. Nice stop gap.

Is there a way to automatically execute the macro every time I drag a particular field onto a particular section of the pivot table? Or is manual execution the only way?

Thank you.

**MarvinP** · 03-25-2011, 12:19 PM

I don't know of an event that triggers when you drag a name on the Pivot List. You might trigger the event to rename that cell each time the worksheet is activated or perhaps when ever a cell is selected on that worksheet?
Seems like an overkill method to me but it might work.

After wondering if I gave a bad answer I recoded a macro and there is an event called -
http://technet.microsoft.com/en-us/l...hangesync.aspx

which might capture the event you want. It might only work in 2010.