[Excel 2010] Copying filtered data from a sheet to another

How about doing a COUNTIFS formula comparing the values in each row of sheet 2 with the lists in sheet 1?

Those results which are greater than 0 are to be copied. Apply a filter on this column and copy the visible area, pasting into sheet 1 will only copy the visible cells.

Thanks
Rob H

Hi Eric,

In Excel 2007/2010, if you have filtered data visible in a sheet and highlight that data (ex., select all visible rows, select all visible cells, select a subset of visible cells), you can copy that selection (keyboard shortcut; [Ctrl] +[C]) and paste that selection in a new worksheet.  Only those filtered cells will be copied.

This still requires that you manually filter the data in the 2nd sheet and manually copy it to the 1st sheet.

Another alternative might be to create a PivotTable on the 1st sheet and assign "Application" and "OS" as Report Filters.  You could then display a summary of data from the 2nd sheet that meets the criteria you select for "Application" and "OS".

If you wish to automate this function in VBA, you will have to give some more parameters:
* What cells display the Application and OS values on the 1st sheet?
* What cell(s) define where the copied values are to be displayed on the 1st sheet?

Sorry if it wasn't clear, but I want this search result to be automatically refreshed on 1st page.

I attached a file to make it clearer...
 Workbook-ee.xlsx

BTW, normally F6 and I6 are list boxes referring to _values(B2-B7) and (D2-D6)
don't know why they've been removed...

That was helpful.  I have attached a modified version of your workbook. Workbook-ee.xlsm

Two major changes:
1) I copied all the data from the "Full_list" sheet to the "Search" sheet and created an Excel table to allow dynamic range formatting and control.
2) I added an auto-run macro to the "Search" sheet (Worksheet_SelectionChange).  It will run whenever there a change to a cell value.  It will filter the data in the output section to the two values for Country and OS.

Note that if you want to see all values for a given field, enter an asterisk (*) as a wildcard.

I also added data validation for those two cells to point to your lists on the "_values" sheet.

This eliminates the need for the "Full_List" worksheet since all the data resides on the Search sheet now, but I left it for your reference.

Hope that gives you a good start.
-Glenn Ray

Hello Glenn,

Thank you very much for your precious help.

I try to understand what you did :

--> Did you only add the code in Worksheet_SelectionChange ?
Because I can't understand how this code gets the values selected in the two cells (F6 and I6).

--> Instead of performing this update at each click (which can be a bit bothersome), I want to create a button and linking this to the code, how can I do this ?

Thanks!

Erik, did my second solution (with the macro button) work better?

Hi Glenn,

Your solution is perfect.
Thanks for helping a total newbie in VBA, it was really easy to understand this !

Cheers,
Eric