Losing Fields when clearing a Pivot Table

[jomili]

Our QA team creates a workbook which contains a pivot table linked to an external data source. My team has no link to the datasource, other than through the pivot table. We can reconfigure the pivot any way we want.

Our team has to use the pivot table to do a comparison with another data source. To enable the comparison, we have to reconfigure the Pivot table. Since there's a lot of fields to reconfigure, and there are 10 of us throughout the state, and we each have to do it daily, I've written a macro to do it for us. It moves/brings in/out the fields we need.

My macro works well, except when the QA changes the "look" of the pivot table for their purposes. So all of a sudden my macro bombs because an expected field isn't where it used to be.

I thought I had an answer to this problem with the below code:

ActiveSheet.PivotTables(1).ClearTable

My thought was that I could clear the pivot, and rebuild it with the constituant fields, to make it the way I want it, no matter what changes the QA team does.

But, I have a problem; when I first go into the pivot table, there's a field (FUND_PosnFunc) that is critical to the data I need to extract. But, if I run the above code, while most other fields remain, the FUND_PosnFunc field disappears.

So, my question is, how can I both clear the pivot field AND retain all the fields it had before I cleared it?

Thanks in advance.

BTW, it doesn't help if you tell me to tell the QA team not to make changes. If it was that easy I would have done it long ago.

[humdingaling]

could you not cleartable and re-add the field?

    ActiveSheet.PivotTables(1).ClearTable
    ActiveSheet.PivotTables(1).CalculatedFields.Add "FUND_PosnFunc", "=Marks /100", True

i just random simple field marks/100 in my example

[jomili]

Never mind, I'm just being stupid. The field IS there, it just has a different name. I'm okay now. Thanks.