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.
--
Maki @ Canberra.AU

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.

--
Biff
Microsoft Excel MVP


"Maki" wrote in message
...
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.
--
Maki @ Canberra.AU

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

"Maki" wrote:

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.
--
Maki @ Canberra.AU

1. I have just tested T Valkos suggestion to start from another sheet and,
as far as I can see, that doesnt 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.




"Maki" wrote:

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.
--
Maki @ Canberra.AU

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.

"trip_to_tokyo" wrote:

1. I have just tested T Valkos suggestion to start from another sheet and,
as far as I can see, that doesnt 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.




"Maki" wrote:

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.
--
Maki @ Canberra.AU

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 DataFilterAdvanced 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.

--
Biff
Microsoft Excel MVP


"trip_to_tokyo" wrote in message
...
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.




"Maki" wrote:

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.
--
Maki @ Canberra.AU

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

"T. Valko" wrote:

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 DataFilterAdvanced 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.

--
Biff
Microsoft Excel MVP


"trip_to_tokyo" wrote in message
...
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.




"Maki" wrote:

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.
--
Maki @ Canberra.AU


.