MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Autofiltering data to a new worksheet

Posted by Hong on December 23, 2000 2:06 PM

Hi All: I have a workbook with a Master Worksheet that links to other worksheets in this same workbook. What I'd like to do is to use "AutoFilter" to extract data from this Master Sheet and output the results to a new sheet in this same workbook with all related information (ie. rows and colums) [ie. Get data from column "STATUS" with the word "READY", output all rows/columns to a sheet called "RESULTS"].

If "autofilter" would not be good enough (I tried with no success), could you please let me know what would be the best way and how to do it?

Any help/pointers would be greatly appreciated.

Happy Holidays,


Posted by Robert on December 29, 2000 11:42 AM

There are two ways (at least) to do this:

Way one: generate a pivot table from your data, select from the field the data you want to be included, double click on the data number in the pivot table and a new worksheet is created with only your selection

Way two: write a little vba porocedure which loops through your sheet, copies each row which is visible to a new sheet. The property you check for is hidden and is false if the row is visible.

Does this help enough or do you need more detailed solution?