Copy row from one worksheet to another with multiple column criteria

Alex R

New Member
Joined
Jan 11, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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 seperate worksheet.

1610403120195.png


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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
79
Office Version
  1. 365
Platform
  1. Windows
try this
data In column A-fruit , B-colour and C-size on sheet1 copy to sheet2
VBA Code:
Sub copy_if()
'COPY ENIRE ROW IF COLUMN B (2) CONTAINS "GREEN" AND COLUMN C (3) CONTAINS "MEDIUM""
   Dim LastRow As Long
   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   With ActiveSheet
      .Range("A1:c" & LastRow).AutoFilter 2, "green"
      .Range("A1:c" & LastRow).AutoFilter 3, "medium"
      .AutoFilter.Range.Offset(1).Copy Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
   
      .ShowAllData
   End With
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,454
Messages
5,601,764
Members
414,471
Latest member
cchunyadi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top