Quicker than Auto Filter

[rae30]

Mr Excel Guru's

If on Sheet 1 I had a data sheet, with 1000 lines of data.

How can I automatically list all rows in Sheet1 that in ColumnA = name of current sheet.

ie sheet2="Smith" list all six Smith occurances
Sheet3="Jackson" list all 15 Jackson occurances
etc.

I know how to use the autofilter, but I was wondering if there is a quicker way to do this, as I could have 10-15 sheets to copy and paste.

Ray

 

[Greg Truby]

Honestly, Autofilter's pretty quick. But here's an alternate way - create's a bit of chaff to discard (specifically extra WS's) - but it's also reasonably easy:

1. Set up a Pivot Table with the field you want to split on as the Page field and any field that suits your fancy as your data item. No row items, no column items, i.e. your PT has just one box that's some count/sum for everything
2. On the PT toolbar (in XL2002 it's on the "PT Button" on the PT Toolbar) find Show Pages... and tell it to Show Pages on your Page field.
3. Use Ctrl+PgUp/PgDown to hop from page to page and double-click the summary field (should be cell [B4]) on each PT. And voila.
4. Now just delete the PT sheets. (By holding down the Ctrl key while clicking the tabs to delete and you can kill half-dozen or more at a time

Like I said, a bit of chaff gets produced, but it has the benefit of automatically creating a listing for each unique value in your listing field. While the PT page sheets get a name you'd want, the drill-down sheets don't, so would have to rename them by hand. And if you're comfortable with VBA, you'll notice that this would not be too hard to code into a macro.

 

[rae30]

Thanks for you assistance,


Ray