Solved

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

Posted on 2011-09-13
9
263 Views
Last Modified: 2012-05-12
Hi there,

I have a 2-sheet workbook.

1st sheet with two value selections "Application" and "OS" with 2 lists of predefined values.
2nd sheet with many lines of data which I want to copy in the 1st sheet,only if some conditions are fulfilled (if the "Application" and "OS" values are matching the ones selected in the 1st sheet)

I don't know if i have to use VBA or if Excel has already a feature for that.

Thanks !

Eric
0
Comment
Question by:eirikur
  • 4
  • 4
9 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36530720
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
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36530804
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?
0
 
LVL 1

Author Comment

by:eirikur
ID: 36530825
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:eirikur
ID: 36530882
BTW, normally F6 and I6 are list boxes referring to _values(B2-B7) and (D2-D6)
don't know why they've been removed...
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36531512
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
0
 
LVL 1

Author Comment

by:eirikur
ID: 36534998
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!
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 36537292
Hi Erik,

Sorry for the delay in my reply; having difficulty getting into EE today.

You wrote--> 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).

Yes; the only code I added was that.  Did you look at the code in VBA?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.AutoFilterMode = False 'this turns off any filters to reset it
    Range("B8:M8").AutoFilter 'turns them back on
    Range("B8:M8").AutoFilter Field:=2, Criteria1:=Range("Country").Text 'this sets the Country field to the value in cell F6, which was given the range name "Country"
    Range("B8:M8").AutoFilter Field:=4, Criteria1:=Range("OS").Text ' this sets the OS field to the value in I6, which was given the range name "OS"
End Sub

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

I can see why you would want that; the table doesn't refresh after a a drop list selection and will always refresh when any cell is selected.  To change this, you would move the VBA code from the worksheet (Feuil1 (Search)) and insert it in a new module and then give it a new name.  Then add a button and assign it to the new macro.  

I went ahead and updated the workbook to show this and have attached it here.  Hope that helps you!
-Glenn Workbook-ee.xlsm
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36546489
Erik, did my second solution (with the macro button) work better?
0
 
LVL 1

Author Closing Comment

by:eirikur
ID: 36548568
Hi Glenn,

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

Cheers,
Eric
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
stuck in average-1 12 65
populate  some cells after data verification 45 26
VBA Delete selected Worksheets of each Workbook in a Folder 4 29
NEED TRANSFER  DATA 59 22
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question