HELP! Refreshing pivot table & collapsing all new data

[raebritt]

Hello! This is my first post...

I have created a dashboard that refreshes pivot table data daily. The new data is expanded in the pivot table. Is there a way to have this new data collapse like the rest of the data automatically? I know I can go to each line and "collapse" but it is very time consuming and I want the pivot tables to be collapsed and clean for daily viewing by my employees...

Thanks in advance! Rachel

 

[Luke M]

Under the PivotTable ribbon, Options, ActiveField group, you can just click on the "collapse Entire Field" button to collapse them all. Much faster than each line individually.

Using that, if you want, you could create a macro to do the update & collapse, something like:

Sub UpdateCollapse()
 
With ActiveSheet.PivotTables("PivotTable1") 'Change this as needed
    .PivotCache.Refresh
    .PivotFields("Name").ShowDetail = False  'Change the name as needed
End With
 
End Sub

 

[raebritt]

Thanks Luke- The active field is faster than the right click method. I'm still hoping for a way to have all new data collapse after refreshing, without having to manually do anything after the refresh. I'll give the macro a try.

Thanks again, Rachel