Unable to copy selected cells - Autofilter

Hello,

I am using excel 2007. In my dataset, I have following: Column A - Set of locations and Column B: Number of times location appears. When I apply Autofilter to select top 10 locations based on volume, I get the top 10 locations.

But, the problem appears, when I copy the top 10 locations and related volume. By default only top10 locations should be selected, but excel selects all of them.

The problem gets resolved, when I leave the autofilter on and close the excel. When I re-open the same file, I am able to copy top 10 locations and its corresponding volume without any problem.

I don't want to restart excel every time for copying the right data, Can anyone here suggest a better solution please?

Regards,
Amit