Question: In Excel 2003/XP/2000/97, I've filtered my data in Excel and I want to copy the filtered data to another worksheet. How do I do this?
Answer: In newer versions of Excel, the copy of filtered data does not copy the hidden rows. Because of this, we will provide a few scenarios on how to copy filtered data.
Solution #1 - Copy only visible filtered data
Our first solution demonstrates how to copy only the visible filtered data to a new worksheet.
To do this, open your Excel spreadsheet and select the filtered data. Press Ctrl+C to copy the data.
Next, select the worksheet where you'd like to paste the data. Press Ctrl+V to paste the data into the new worksheet.
The data that is pasted will only be the visible data from the filter. The rows that were hidden by the filter will not be pasted.
Solution #2 - Copy visible and hidden filtered data
Unfortunately, you can not copy and paste any of the hidden filtered data with the standard copy and paste functionality inherent in Excel. If you wish to copy all of the data, you'll have to remove the filter before copying.
To do this, select a cell in one of the filtered rows. Under the Data menu, select Filter > Show All.
Now all of the rows should be visible. Select all of the data and press Ctrl+C.
Next, select the worksheet where you'd like to paste the data. Press Ctrl+V to paste the data into the new worksheet.
Now all of the rows should appear in the pasted data. You can now re-apply your filters.
