Copy data with multiple cryteria

[hentie]

Hi, I use the following code to search for cell's and copy the rows with found data to another sheet.

<font face=Courier New>REPORT_DATE:<br><SPAN style="color:#00007F">With</SPAN> Worksheets("DATA")<br>    lLastRow = Worksheets("DATA").Cells(.Rows.Count, "A").End(xlUp).Row <SPAN style="color:#007F00">' last row used in column A</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lRow = 2 <SPAN style="color:#00007F">To</SPAN> lLastRow<br>        <br>        <SPAN style="color:#00007F">If</SPAN> .Cells(lRow, "A") >= FromDate And .Cells(lRow, "A") <= ToDate <SPAN style="color:#00007F">Then</SPAN><br>            Application.Union(.Range(.Cells(lRow, "A"), .Cells(lRow, "I")), .Cells(lRow, "M")).Copy _<br>                Destination:=Worksheets("PRINT-1").Range("A" & Worksheets("PRINT-1").Rows.Count).End(xlUp).Offset(1, 0)<br>      <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lRow</FONT>

<font face=Courier New>REPORT_NCRNO:<br><SPAN style="color:#00007F">With</SPAN> Worksheets("DATA")<br>    lLastRow = Worksheets("DATA").Cells(.Rows.Count, "B").End(xlUp).Row <SPAN style="color:#007F00">' last row used in column B</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lRow = 2 <SPAN style="color:#00007F">To</SPAN> lLastRow<br>        <br>        <SPAN style="color:#00007F">If</SPAN> .Cells(lRow, "B") = ComboBoxReportNCRNo.Value <SPAN style="color:#00007F">Then</SPAN><br>            Application.Union(.Range(.Cells(lRow, "A"), .Cells(lRow, "I")), .Cells(lRow, "M")).Copy _<br>                Destination:=Worksheets("PRINT-1").Range("A" & Worksheets("PRINT-1").Rows.Count).End(xlUp).Offset(1, 0)<br><br>        End <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lRow</FONT>

<font face=Courier New>REPORT_FAULT:<br><SPAN style="color:#00007F">With</SPAN> Worksheets("DATA")<br>    lLastRow = Worksheets("DATA").Cells(.Rows.Count, "E").End(xlUp).Row <SPAN style="color:#007F00">' last row used in column E</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lRow = 2 <SPAN style="color:#00007F">To</SPAN> lLastRow<br>        <br>        <SPAN style="color:#00007F">If</SPAN> .Cells(lRow, "E") = ComboBoxReportFault.Value <SPAN style="color:#00007F">Then</SPAN><br>            Application.Union(.Range(.Cells(lRow, "A"), .Cells(lRow, "I")), .Cells(lRow, "M")).Copy _<br>                Destination:=Worksheets("PRINT-1").Range("A" & Worksheets("PRINT-1").Rows.Count).End(xlUp).Offset(1, 0)<br>        End <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lRow</FONT>

I use more than these three "search type's" in my sheet, what I need help with is to sometimes combine more than one of them together.

At the moment I can only search for REPORT_DATE or REPORT_NCRNO etc. How can I change my code to search for say, a row where three of the columns must have the right data in before it is copied to another sheet.

I get the data that I search for from drop down list's. There are 12 different drop down list's.

Hope it is clear what I am trying to do.
I will appreciate all help.

 

[pbornemeier]

Pull the settings from the drop down lists and use them to establish an autofilter (up to 2 criteria/column) or a filter (unlimited criteria for each column).

Turn on macro recorder and play with filter/autofilter to see what kind of code you will need to have.

Once desired rows are visible, highlight entire range and use Edit | Goto | Special | Visible Cells Only, then copy and paste

 

[hentie]

Thank you for the advice, I got code working for two cryteria filter. Need to work on it still.