# IF-Then statement to copy rows

Solved/Closed
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 - Dec 28, 2010 at 01:59 PM
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 - Jan 4, 2011 at 10:54 AM
Hello,

I am wondering if there is a way to copy a specific row from one worksheet to another in the same workbook based on an IF-Then statement. What I want to do is first look at the plan year, then the plan type. If these match a certain criteria then I need to copy and paste that entire row to a certain worksheet.

Here is an example...

....A.................................B..........................C
1 Plan Name.................Plan Year...............Plan Type
2 Minor Child.................2010......................Minor
3 Minor Child.................2011......................Minor

If the plan year = "2010" and plan type = "Minor", then copy entire row to worksheet "Minor Child 2010"
If the plan year = "2011" and plan type = "Minor", then copy entire row to worksheet "Minor Child 2011"
If the plan year = "2010" and plan type = "Adult", then copy entire row to worksheet "Adult Child 2010"
If the plan year = "2011" and plan type = "Adult", then copy entire row to worksheet "Adult Child 2011"

Thank you and let me know if you have any questions,

## 1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 30, 2010 at 09:44 AM
Hi Froggy,

Try this code:
```Sub test()
Set MR = Sheets("Input sheet").Range("B2:B5")

For Each cell In MR

If cell.Value = 2010 And cell.Offset(0, 1).Value = "Minor" Then
cell.EntireRow.Copy
Sheets("Minor Child 2010").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Value = 2011 And cell.Offset(0, 1).Value = "Minor" Then
cell.EntireRow.Copy
Sheets("Minor Child 2011").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Value = 2010 And cell.Offset(0, 1).Value = "Adult" Then
cell.EntireRow.Copy
Sheets("Adult Child 2010").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Value = 2011 And cell.Offset(0, 1).Value = "Adult" Then
cell.EntireRow.Copy
Sheets("Adult Child 2011").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If

Application.CutCopyMode = False

Next

End Sub```

Can you work with this?
Ask away if you get stuck?

Best regards,
Trowa
I think I'm trying to do the same thing. I tried modifying the code, but it wouldn't work. What i'm trying to accomplish is moving data from a master sheet to sub-sheets based on the value of the first cell:

property info1 info2 info3
A 1 2 3
B 2 3 4
C 3 4 1
D 4 1 2

In this example, if the row is for Property A, it will copy the entire row from Sheet 'Master' to Sheet 'A'

I've never done this before, so I don't even know how and where to execute the code. What I came up with:

Sub Data_move()
Set MR = Sheets("Main").Range("a2:a8")

For Each cell In MR

If cell.Offset(0, 1).Value = "A" Then
cell.EntireRow.Copy
Sheets("A").Range("a" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Offset(0, 1).Value = "B" Then
cell.EntireRow.Copy
Sheets("B").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Offset(0, 1).Value = "C" Then
cell.EntireRow.Copy
Sheets("C").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Offset(0, 1).Value = "D" Then
cell.EntireRow.Copy
Sheets("D").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If

Application.CutCopyMode = False

Next

End Sub

What am I doing wrong?
Ok, I got rid of the Offset and just left it a value and it seems to be working. Next problem. I need this to automatically copy info from the Master Sheet to the sub-sheets. Right now, I have to run it manually, which creates duplicate entries on the sub-sheets every time I run it.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 4, 2011 at 10:04 AM
Hi Mike,

Running the code automatically doesn't solve the duplicate issue.

I can think of two ways to solve this issue.

1. Add a code line to remove the data from the destination sheets first:
```Sub Data_move()

Sheets("A").Cells.ClearContents
Sheets("B").Cells.ClearContents
Sheets("C").Cells.ClearContents
Sheets("D").Cells.ClearContents

Set MR = Sheets("Main").Range("a2:a8")
```

Downside is that when you have a lot of data, the code will take a while to run. Adding more data and the code takes even longer.

2. Actively select the range you want to move:
```Sub Data_move()

Set MR = Selection

For Each cell In MR ```

Downside here is that you have to pay attention to the range you select in order to prevent mistakes.

Hopes this helps.

Best regards,
Trowa
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 2
Jan 4, 2011 at 10:54 AM
That worked great, thank you for your help.

Froggy