Copy AutoFilter Data 2007

In 2003 when you use autofilter and then tried to copy the resultant information, you would only get the visible items that remain as a result of the filter. For example if you have a list of employees in alpabetical order in Column A and then in Column B you have the State in which they are employeed. Now if I use auto filter and filter on Column B to get all employees in the state of Ohio, that is all that will be visible on the screen. Perfect that is what I want. Now I want to copy all of these employees and paste them into another document or spreadsheet. (don't forget that because the list is sorted by employee all employees in Ohio are not next to eachother in the list so there are a bunch of rows that have been filtered out. and for the sake of explaining the dilema here we will not resort the list as this is a very basic example of the problem that I am having and resorting the list may not work in every case.)

In 2003 I would simply select the top left cell in the list and hit Control+Shift+End to select the all of the results and then simply copy this over to another spread sheet and I will get exactly what I was looking for. In 2007 if I go through this same process, and paste the data to another location, all of the data, even the rows that were filtered out, will come through. The whole purpose of the autofilter is to weed out what you don't want. However in 2007 this does not work in 2007.