Excel - filter data into another sheet 1

[Andrzejek]

Let's say I have some data in my Sheet1:
[tt]
A B C
1 FName LName Gender
2 John Smith M
3 Barb Brows F
4 Susie White F
5 Pat Black M
6 Joe Blue M[/tt]

I know I can do: Data - Filter - Auto Filter ...
Choose M in column C, filter all M's, copy all of that, move to Sheet2, paste it, and repeat all of that with F's

How can I do all of that in VBA?

I know I can record a Macro, but is there a better way to filter all Males and all Females into their own sheets in Excel VBA?


Have fun.

---- Andy

 

[SkipVought]

hi,

Use either MS Query or PivotTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Another option is Advanced filter. To extract filter results to a different sheet you need to use named ranges.

Gavin

 

[GlennUK]

Another option is Advanced filter. To extract filter results to a different sheet you need to use named ranges.
Gavin

... are you sure about using named ranges? In what way? ( I don't, and it works fine for me ... but I may be misunderstanding you, hence the query )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Andrzejek]

I was hoping for some easy (read: fancy) Excel way of doing it, but since I use ADODB recordset it was just as easy to move to next sheet and use Filter on my recordset:
[tt]
rst.Filter = "Gender = 'M'"[/tt]

Thank you anyway...

Have fun.

---- Andy

 

[Gavona]

Hi Glenn,
In Excel 2003, without named ranges you can only extract the filtered data to the active sheet. You CAN make it work provided you instigate advanced filter from the destination sheet, have more than one cell selected at the time and (if you only have heading cells in your extract range) don't get confused by the warning message. I seem, to remember it was impossible in earlier versions.
Using named ranges certainly makes it easier!

Whether all the above applies when using VBA, I have not tested recently.

Regards

Gavin

 

[GlennUK]

Thanks. I usually use named ranges anyway, but was experimenting with Excel 2007 yesterday, and was curious.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

Excel 2003 is not happy about sheet names in advanced filtering

VBA is ok with it as it results in a range object in any case

2007/2010 seem fine with whatever

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question