Differentiate between column fields and data fields in a pivot table

Hello,

I am trying to manage the format for a pivot table that we receive from a customer every week. In order for my macro to function correctly, I need to be able to format the pivot table to the expected format, with the expected pivot row fields and pivot column fields. I've been able to do this for rows fields, and for format using some code for autoformat, etc., but not for data the column fields.

The data fields change once a month (which is expected and compensated for in my macro), but I need to be able to remove any column fields without removing any data fields using VBA.

Therefore, I have tried the following code to remove column fields:

Please Login or Register  to view this content.

However, this also for some reason deletes my fields in pivotdata as well.

To compensate, I tried the following code:

Please Login or Register  to view this content.

However, now it will not remove any column fields, thinking that they are data fields.

Is there any way in VBA to systematically differentiate between column fields and data fields so I can remove column fields while leaving data fields as is?

Thanks.

Regards,

William

Hi All,

I found my answer. Apparantly (at least in Excel 2003 and I'm assuming the same in Excel 2007), if more than one pivot field is loaded as data, than rather then having a pivot field in the data fields area, Excel makes a pivot field called Data and places it in the columns area.

Thus, all I needed in my code was

Please Login or Register  to view this content.

Thanks.