Previous Topic Next Topic

IF-Then statement to copy rows

Solved/Closed
froggy6703 Posts 16 Registration date Tuesday 23 March 2010 Status Member Last seen 29 March 2013 - 28 Dec 2010 à 13:59
froggy6703 Posts 16 Registration date Tuesday 23 March 2010 Status Member Last seen 29 March 2013 - 4 Jan 2011 à 10:54
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,


1 response

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
30 Dec 2010 à 09:44
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
Mike
30 Dec 2010 à 12:21
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?
Mike
30 Dec 2010 à 12:30
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 12 September 2010 Status Contributor Last seen 27 December 2022 555
4 Jan 2011 à 10:04
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 23 March 2010 Status Member Last seen 29 March 2013 2
4 Jan 2011 à 10:54
That worked great, thank you for your help.

Froggy

Similar discussions

Copy row if a range of column matches a value
NoraRoberts -
scottrm20 -

23 responses
Code to move rows into another sheet based on certain values
nathaliecannell -
vcoolio -

1 response
Copy rows based on a condition
Blossom83 -
ravichennai86 -

30 responses
Copy data onto sheet only if cell has text
danipants -
checjb -

17 responses
Excel - To generate unique ID
wliang -
ferg -

4 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses