I want to extract info from a database on sheet1 to sheet2.

[Caz22]

I have followed the below instructions:

1. Go to Sheet 2
2. Select a cell in an unused part of the sheet (cell C4 in this example).
3. From the Data menu, choose Filter, Advanced Filter.(In Excel 2007,
click the Data tab on the Ribbon, then click Advanced Filter.)
4. Choose Copy to another location.
5. Click in the List Range box
6. Select Sheet 1, and select the database.
7. (optional) Click in the Criteria range box.
8. Select the criteria range
9. Click in the Copy to box.
10. Select the cell on Sheet 2 in which you want the results to start, or
select the headings that you have typed on Sheet 2.
11. (optional) Check the box for Unique Values Only
12. Click OK

Once I have followed (I think!!) the above, I click ok and get an error
message saying "You can only copy filtered data to the active sheet."

Any advise would be gratefully received. I am very new to excel so be gentle!!

 

[Sheila D]

This works fine for me, are you definitely on Sheet 2 when you choose the
Advanced Filter command? I never knew you could do this at all so thanks!

 

[Gary Brown]

I think you have to perform the paste in the same worksheet.

Try these steps...
1. Insert a new worksheet. In this example, called SHEET3.
2. Go to Sheet 1 and select the database.
3. Copy the database to SHEET3
4. From the Data menu, choose Filter, Advanced Filter.(In Excel 2007,
click the Data tab on the Ribbon, then click Advanced Filter.)
5. Choose Copy to another location.
6. Click in the List Range box
7. In SHEET 3, select the column in the database that you want to get
unique values for (including the title).
8. (optional) Click in the Criteria range box and select the criteria range
9. Click in the Copy to box.
10. Select a cell on SHEET3 in which you want the results to start.
11. (optional) Check the box for Unique Values Only
12. Click OK
13. Copy the results to the Sheet location desired.
14. Delete SHEET3.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Gord Dibben]

Gary

The instructions OP posted are correct for copying unique data to another
worksheet and were copied directly from Debra Dalgleish's site

http://www.contextures.on.ca/xladvfilter01.html#ExtractWs

Don't need anything other than a sheet with data and a sheet to copy filtered
results to.

So your advice to copy the entire database to a third sheet then deleting later
is just a bunch of unnecessary steps.


Gord Dibben MS Excel MVP

 

[Gary Brown]

Understood Gord but she said that wasn't doing it for her. I agree it would
be unnecessary IF the directions were working for her, but unfortunately,
they weren't. I was giving her an alternative to try.

 

[Gord Dibben]

Alternatives are OK but I think OP should re-trace steps taken.

It will work as Debra has instructed so why use a convoluted workaround?


Gord

 

[Gary Brown]

Understood, but I felt that since we're trying to help each other, instead of
simply saying 'Well try it again because you obviously didn't do it right to
begin with', I would give her 2 extra steps that would possibly help her out.