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