Distributed Pivot Table Erases Fields & Columns on Refresh

I have a pivot table referencing a dynamic named range (DNR) within the workbook (not referencing content in another workbook). When I update the content in the DNR on my local, the pivot table refreshes properly. When I send the file to my customer, the same updating steps result in the pivot table removing all value and columns fields from the pivot table upon refresh. I have confirmed that the DNR is still referencing the same data range on their end. It is not broken. Additionally, the DNR is not referencing an external workbook. It is referencing content within the same workbook.

I am at a loss as to why this problem would arise on a different machine.

The attached image shows the Field List before (left) and after (right) the refresh on the customer's machine.

beforeandafter.png

Dynamic named range (DNR)

The value fields are gone, as though they were deselected.


We are both on 2010. Apologies for not including that originally.

No. It's like the pivot itself shrinks down to a one-column table.
If I select the cells where the values used to show up, the pivot option menu doesn't even appear in the ribbon bar.

I should add that this happens both before and after they run their updates.
I think I stated otherwise in my OP. Just the presence of the file on their machine causes the refresh option to behave this way.

Just gonna bump this one time and then I'll let it go

I haven't gone there physically: They are in Toronto, and I'm in St. Louis.
However, we did everything over a screen share. I witnessed all of it as the customer followed my instructions.

But that does spur a thought. I had to send the file through my company's large file distribution network. It's possible that could have messed the file up somehow. I will send it to myself and see if it causes the same issue on my machine.

Looks like distributing the report has nothing to do with the issue. Originally, I thought there was no bug with my local version, but now I am recreating the bug perfectly on my end pre-distribution.

I might have a bead on the problem.

Part of the updating process for this workbook involves copying the page with the dynamic named range and storing it as a temporary backup. This backup process creates a copy of the DNR with the same name as the original but a scope set to the name of the new worksheet. My backup process also deletes some unnecessary columns from the backup. Those columns are what are disappearing in the pivot table and are not even available for selection in the field list. I have double-checked that the pivot table is definitely referencing the original DNR that is scoped to the entire workbook, but that doesn't seem to fix the issue.

I'm going to go back and have the code remove the copied DNR before any editing takes place, but I'm curious if anyone else has seen a similar issue in the past.

I thought I answered it, which means I don't understand exactly what you are looking for.

Are you asking about what is shown when I open the 'Source Data' menu in Pivot Table > Options?

It is the name of the named range: 'e2e_all'

I have confirmed my earlier suspicions that the existence of multiple copies of the same named range are to blame here.
In testing, I have re-created the processes that copy the worksheet, including the named range, but I have re-titled the named range copies before refreshing the pivot table.
In that scenario, the pivot table does not lose the value fields.
If I leave the named range copies unedited, the pivot table jettisons the value fields.

Thanks to those who took a moment to chime in.