IF-Then statement to copy rows [Solved/Closed]

Posts
16
Registration date
Tuesday March 23, 2010
Last seen
March 29, 2013
- - Latest reply: froggy6703
Posts
16
Registration date
Tuesday March 23, 2010
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
4 Adult Child.................2010......................Adult
5 Adult Child.................2011......................Adult

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,


See more 

1 reply

Best answer
Posts
2435
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 4, 2018
1
Thank you
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

Thank you, TrowaD 1

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2564 users this month

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
2435
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 4, 2018
-
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
Last seen
March 29, 2013
-
That worked great, thank you for your help.

Froggy