Refresh and format Pivot to show top score

[happydays886]

Hello,

I have a tool I'm working on that I've hit a dead-end on and could use some help.

I want to be able to summarize top score from the corresponding data set. I tried using formulas, but I couldn't get around the change in ranking and duplications. That's why the table is pulling from a pivot: ranking and duplications are somewhat clean.

In order to make this work for anyone unfamiliar with Excel, I added a button that refreshes the pivot detail. This seems to work (some times), but now I'm faced with an issue of new information breaking the process: filters get reset.

The area highlighted yellow shows where this tool breaks. In that area, I added "JUNK" in the data set with the expectation that its corresponding total score of "10" would move to the first rank; because of the filters/sort are reset when the button is clicked, JUNK moves to the bottom of the pivot table and is not reflected on the total score page as a result.

Can someone show how you would fix this to make it work, or show a better solution if one is out there?

Thank you!