Pivot table Structure Changes When Data is Updated

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.

June 25th, 2013 6:02pm

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.

Free Windows Admin Tool Kit Click here and download it now
June 26th, 2013 12:19pm

Thanks Jaynet for your reply.  I shall have a go and see if that works.
June 26th, 2013 1:32pm

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

Free Windows Admin Tool Kit Click here and download it now
July 4th, 2013 9:26am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics