Copy row from one worksheet to another with multiple column criteria

Hello! I'm looking to copy an entire row from one worksheet (Sheet1) to another (Sheet2) based on multiple criteria.

For example, in the image below, I need to be able to find all the "Green" fruits and then refine that further to only "Medium" Size. Then, I need to copy the entire row (as long as it has both criteria) into a separate worksheet.Capture.JPG

This is a simplified example but currently my code looks something like this:

Private Sub CommandButton_Click()
Dim c As Range
Dim j As Integer

Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("Input") 'name of the actual worksheet
Set Target = ActiveWorkbook.Worksheets("Output") 'name of the actual worksheet

j = 13 'need it to start looking at row 13 of Sheet1
For Each c In Source.Range("B1:B1000") 'needs to work for column B (in the simplified example, search every row in the column "Colour")
If c = "Green" And source.range(c, 3) = "Medium" Then 'find all rows that have both Green (from column B) andMedium (from column c, i.e. 3)
Source.Rows(c.Row).Copy Target.Rows(j) 'copy the rows that match the criteria above to a new worksheet (preferably row 7 if at all possible)
j = j + 1 'repeat for next row!
End If
Next c
End Sub

Many many thanks in advance.

Here is a slightly different approach to meet the same ends.

Please Login or Register  to view this content.

This is great! Thank you!!

Is there a way to modify the code slightly so that it doesn't copy to row 1 of Sheet2? I have a header that I don't want overwritten.

Sure, glad it works.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

One last complication/challenge. Once again I'd greatly appreciate your help.

I have a screenshot of what my actual code / spreadsheet looks like attached.

Basically, I have a userform where you can input:
File Type / Property Details / Floor Type / Wall Type / Roof Type / Foundation Type / Miscellaneous

This inputs the selected values to cells AA1 - AA7 of Sheet 2 and if I have the exact match for all of them, the code below works great (huge thanks to you).

However, I need to to work so that if any input is left blank, let's say AA3, the filtering only applies to the range with values. In the same image, I have a couple examples.

Attachment 712937

The last column lists (from a dropdown using code) multiple additional features so for filtering in the last column, the code needs to work so that if the value in cell AA7 is anywhere in the cell, it retrieves the row.

Hello, Please see attached. Thanks!