Refresh All Does Not Collapse Pivot Table

[calvinle]

Hi,

I have a pivot table with pivot chart, and I use VBA to reinsert new data into the table as I want to bypass the need to enter the password everytime it pulls data from the Access tables.
After the new data has been added to the table, I use the following code to refresh the Pivot Table:

Please Login or Register  to view this content.

When I do it for on a new day, the pivot table does not automatically Collapse the Date, so it creates an ugly double date name in the Chart.

Is there a way to force Collapse after refresh?

See screenshot for before and after:

[teylyn]

Hello,

if you don't want to expand the months, have you considered removing the fields that are nested underneath the date? If you don't nest anything, there's nothing to expand or collapse in the first place.

Maybe you can post a small data sample with the setup for inspection.

cheers, teylyn

[xlnitwit]

Hi,

You might perhaps loop through the rowfields thus

Please Login or Register  to view this content.