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" <(E-Mail Removed)> wrote in message
news:EF997185-B18E-424D-9F50-(E-Mail Removed)...
> 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 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

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

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

--
Biff
Microsoft Excel MVP


"trip_to_tokyo" <(E-Mail Removed)> wrote in message
news7F24F12-CF0C-45A3-B735-(E-Mail Removed)...
> 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 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.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "trip_to_tokyo" <(E-Mail Removed)> wrote in message
> news7F24F12-CF0C-45A3-B735-(E-Mail Removed)...
> > 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
>
>
> .
>