Pivot Sort by values in FIXED Selected Column

[Jabba69]

Hello.

I hope someone can help me as this is driving me crazy!

I have a fairly simple pivot table (linked to an external source). It has date in Mth-yy format (sorted by newest (left) to oldest) in column headings, Brand in Row, and some brand share value in Values.

I have selected it to sort Brand (rows) by values in selected column $D$5. This works fine, however when I refresh the pivot table and more months are added, the reference changes so its still sorting by the original month above (eg $E$5).
I need to always sort by the latest month - which will always be column D.

Any help would be greatly appreciated!


Oh and another weird point - I have another pivot in a separate workbook which is almost identical, and this one does fix the reference when refreshed!!
I've gone through all the pivot options etc, and cannot seem to find anything different.

Thanks

[Jabba69]

Still no luck with this and I seem to have exhausted all avenues.

I've checked all Excel Formula and Advanced options and all are the same on both workbooks, as well as all Pivot table options, and making sure the design is the same etc.



I've had to come up with a workaround - some pivot change code that automatically sets resets the 'Sort by values in column' to $D$5 after each refresh.
Simple enough and does the job, but ideally I'd like all my reports to work in a standardised way. And more to the point, its just frustrating I cannot work it out!!!

If anyone has any thoughts, I'd be most grateful. Thanks