How to change source name without column deleting when pivot table is refreshed

I have 36 columns (and a lot more rows) of data that I created 50 pivot tables from. Each year, my header (source name) titles change. When I change the source name and refresh the pivot table, the source names that were changed disappear from the PT.
Typically, I would just add the new name/header info, but since there are 50 pivot tables pulling from the data, that will be a monotonous nightmare.
Is there any way to change header titles (source names) without it messing up my PTs?
Changing the name on the PT alone will not solve my problem. The titles are related to the Previous year, Current Year, and Future year. The headers must change on the data source and the PT.
Am I making any sense? Sure sounds good in my head.

You'd have to use code, I think, to add the new fields to the pivot tables. It would be hard to write without an idea of the layout of your workbook - can you post a sample workbook showing where the columns actually are and what needs to be updated?

I created a sample (basically just deleted some data). The pivot tables are on the Summary tab and the Monthly tab. This one only has 10 PTs on the second tab, but I have another that has 50 PTs. The executives want to be able to see all division totals on one sheet, which is why there are so many PTs. The tables are all from the same data on the first worksheet. I am just filtering different divisions, program codes, and fund codes... if that makes sense.

What I need to update is on the Spend Plan Details tab cells O4:Q4. Those headers change throughout the year (as timelines are hit) and at the beginning of each new year. When I change those, I have to re-add the "new" header into my PT on the Summary tab and the Monthly tab. That wasn't terrible to do when I only had a few PTs, but with 50 in one workbook and 10 in another, it would just be great if there was an easier way than manually re-adding three columns to 60 PTs.

Please find the sample workbook attached, and thank you in advance for any help you may be able to provide.

Sorry I will try and look at this tomorrow - I am blocked from this site for most of the day currently.

Thank you. I appreciate any help you can provide.

OK, here's a revised workbook. I tidied up some of the code and added a routine in a new module to update the pivots on the Summary tab. Can you make sure that works OK, and then let me know where the fields should appear in the pivot on the Monthly tab?

The code looks amazing! I'll be honest, it's so far above my head, I don't even understand it.

When I make the update to the headers on the Spend Plan Details tab and run the amazing macro you wrote, I get an error: Run-time error: 1004 "Unable to get the PivotFields property of the Pivot Table class."

When I click debug, this is highlighted: [Set pf = .PivotFields(vHeaders(1, n))]

I wish I knew what to do, but as I said, way above my head. Can you help? I really, truly appreciate your help.

I think I found the solution to that issue, but now I'm getting this:

Run-time error 1004: Unable to set the Position property of the PivotField class
And when I click debug, this is highlighted: pf.Position = n

Sorry - I have little access to the forum at the moment, but I promise I will try and look at this again tomorrow!

OK can you replace the UpdateSummaryPivotTables routine with this version:

Please Login or Register  to view this content.