Copy only visible cells after filter is applied/ sum after filter
 

I started to use Excel 2007. I find it different from Excel 2003. What I am
doing is setting up a custom filter. Then I need to copy all the data visible
with filter applied to another spreadsheet. Excel 2007 copies all the data
irrespective of the filter.

In Excel 2003 I was able to sum based on only cells visible with filter
applied. In 2007 the sum will include all the cells irrespective of filter.

What is the fix to this? Is there a fix? What am I doing wrong