Advanced Filter - Copying to location other than current sheet?

[Maki]

Hi

Is there a way to get around Excel not being able to copy the advanced
filter results to other than current worksheet?

Thank you.

 

[T. Valko]

Start the filter operation from the destination sheet. If the data to filter
is on Sheet1 and you want the filtered data to be copied to Sheet2, start
the filter process from Sheet2.

 

[trip_to_tokyo]

Why don't you just copy the results of the advanced filter? so if they place
the results in cell M 21 of Sheet1 (for example) then place this formula:-

=Sheet!M21

- in (for example) cell A 1 of Sheet2.

This way you will get your results placed where you want them.

If my comments have helped please hit Yes.

Thanks

 

[trip_to_tokyo]

1. I have just tested T Valko’s suggestion to start from another sheet and,
as far as I can see, that doesn’t work.

2. I have just set up an Advanced Filter and the results are shown in a
Worksheet called:-

PivotTableData

- in cells A 30 to K33.

3. Highlight cells:-

A 30 to K 33 (or the range that you have where the Advanced Filter results
are shown). Then:-

Ctrl-C

- to copy.

4. Go to Sheet2 (for example) cell A 1. This is where you want the Advanced
Filter results to be.

5. Home tab / Paste / Paste Link.

You will now get the results of your Advanced Filter where you want them to
be (and not where EXCEL automatically places them).

If my comments have helped please hit Yes.

Thanks.

 

[trip_to_tokyo]

Have re-tested T Valko's suggestion and it does work. It is a better solution
than mine in that it will pull in the formatting as well.

 

[T. Valko]

1. I have just tested T Valko's suggestion to start from

another sheet and, as far as I can see, that doesn't work.

Try this...

On Sheet1

A1 = Header
A2:A10 = random numbers, make sure some are <50 and some are >=50

On Sheet2

A1 = Header
A2 = >50

While on Sheet2 goto Data>Filter>Advanced filter
Select Copy to another location
List range: Sheet1!$A$1:$A$10
Criteria range: $A$1:$A$2
Copy to: Sheet2!$C$1
OK

Works just fine for me.

 

[trip_to_tokyo]

"Works just fine for me" Agreed, you are quite right Biff.