Strange Behaviour Pivot Table Date Filter Sort

[Jabba1963]

Hi All,


Wondering if anyone can shed some light...


I have a pivot table - I clear filters and set one filter - On a date filter I clear the (blank) pivot item - Then I Sort the date filter descending.


But the list is not always in the correct order until I re-sort ascending and re-apply a descending sort of the same filter... and only then are the results correctly listed in the order I require.


The issue remains if I just apply the steps manually...


Just seems strange to me and I just wondered if I was missing something... or if anyone else has ever come across this one or could shed some light.

[pre]

ActiveSheet.PivotTables("PivotTable4").PivotFields("Value 2").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Value 2").CurrentPage = FilterUnit

With ActiveSheet.PivotTables("PivotTable4").PivotFields("Event Date")
.PivotItems("(blank)").Visible = False
End With

ActiveSheet.PivotTables("PivotTable4").PivotFields("Event Date").AutoSort xlDescending, "Event Date"'

But until I add the following couple lines to the end - it doesn't work !!

'    ActiveSheet.PivotTables("PivotTable4").PivotFields("Event Date").AutoSort xlAscending, "Event Date"
ActiveSheet.PivotTables("PivotTable4").PivotFields("Event Date").AutoSort xlDescending, "Event Date"

[/pre]

Because it works and there is very little overhead and indeed the spread sheet is massive (120mb), I won't be uploading it for detailed analysis... it was just more in passing and if anyone might have an insight... but no great shakes if not.


Thanks

Jabba

Excel 2010.

 

[Debraj]

Hi Jabba!


I know its late.. but still want to remove it from No Reply section!


add a line after sort.. will help you

ActiveSheet.PivotTables("PivotTable4").PivotFields("Event Date").PivotCache.Refresh

Regards,

Deb