Pivot Table with Dynamic Headers

[HURTMYPONY]

This one is a little different than other similar topics.

I have a pivot that bases its data on a fixed number of columns (37).

The problem with the source data is that the column headers, which are values on the pivot, often change their names.

For example, Column W may be named "Sprocket" on one day, it might be changed to "Gear" the next. The number of columns remain the same, but the column headers change.

The pivot does not account for these changes. As soon as the column header changes its name, it removes that column after a refresh (i.e. unchecks that column in the Pivot Field List and that column no longer shows up on the generated pivot).

Is there a way, either in options or by VBA, to get the pivot to include these columns automatically after they change their header name? I tried making a dynamic pivot with OFFSET and a named range, and while it included the newly named column in the Pivot Field List, it does not automatically display the newly named column in the pivot after a refresh.

I fear my end users are not savvy enough to open the Pivot Field List and check the box.

Any solution for this? Thank you very much!

 

[HURTMYPONY]

Doh, I was really expecting this to be one of the easier solutions.

There's really no way to make columns with newly-changed headers appear automatically on an existing pivot table?

 

[RoryA]

Do you have to have the headers change? Would it be an option to use a row above the headers to indicate the current header info and leave the actual headers for the pivot dat the same? You could then use formulas in a row above the pivot to display the correct captions.
If not, you'll need code to add all the new data fields back in.

 

[HURTMYPONY]

Do you have to have the headers change? Would it be an option to use a row above the headers to indicate the current header info and leave the actual headers for the pivot dat the same? You could then use formulas in a row above the pivot to display the correct captions.
If not, you'll need code to add all the new data fields back in.

Thanks for the reply!

That's a great "outside the box" solution that could probably work! Fantastically simple, since the columns never change in number or change in order.

I just have to see if hiding a header on the source data messes with any of the other macro processes, named ranges and dynamic content.

Thanks again.