I have a pivot table in Excel 2010 which is linked to a dynamic range.  Each month our sales team update the data in the range with latest performance.  Lately, I have found that the Pivot table structure is changing during the update process.

On one instance, the Year field and its grouping was deleted.  In another instance the Account name field was deleted.

The pivot table is hidden as it is an intermediary reference table to sort the mass of data by account and month and has calcualated fields for YTD sales, and is referenced by another worksheet which incorporates other data from another worksheet.

Is there anyway to fix the structure so that it does not change, but allows the data to refresh?

The data sheet is updated by macro which cleanses the rawdata and fixes some errors.  Part of the macro code clears down the data sheet and then pastes the new data.  I had thought that this might be causing the problem, but it worked fine plenty of times during trial and also for the first couple of months.

Thanks.

Hi,

I agree with you about the macro maybe caused the issue.

Try to check after the updating, are the fields the same as the previous table.

And try to change the code not to clear down the data sheet, just update the data in the table.

Thanks Jaynet for your reply.  I shall have a go and see if that works.

Hi Jaynet,

Checked the macros and I must have already thought of the issue around leaving data in the data table where the pivot feeds from as the deletion of old data starts from row 100.  So it cannot be that.

The only other thing I can think of is that in some, the format of the cells has changed from General to Accounting.  This could have affected the date column.  Would this then have imapcted the Calcualted and Summarise fields I have in teh pivot table.

I have a YTD calc field and also Summarise by Year and Month field.  It is generally these that disappear.

Your advice is apprecaited.

Thanks