dj44
05-13-2017, 07:07 PM
folks Good evening,
I'm trying to copy some rows,
I have a Worksheet Called "Data". This has all the data.
I have a worksheet called "Copy Rows"
19170
If Column B Cells in worksheet "Data" has a word in it from the list found in Worksheet "Copy Rows"
I would like to copy that row on to Sheet2
I know that you can create an array and then do some auto filtering but it become a bit too much confusing so what I did was I put it in a list, so its easy for me to understand and see my rows that need to be copied.
Now I set it up from all my researching best as I can but I've got stuck on the last bit which is how do I actually copy the Rows now?
Now onto the next sheet.
Sub Copy_Rows()
Dim CopyRowsList As Worksheet
Dim i As Integer
Dim lRow As Long
Dim iCntr As Long
lRow = 400
Set CopyRowsList = Worksheets("Copy Rows") ' << This list has all the rows i would like to copy if Column B has the word in it
For i = 4 To CopyRowsList.Cells(ws.Rows.Count, 4).End(xlUp).Row Step 1
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 2).Value = CopyRowsList.Cells(i, 4).Value Then ' Column B Cell Contains a word from Worksheet "Copy Rows" Column 4 "D"
' Now here is some problems :(
Rows(iCntr).Copy
Worksheets("Sheet2").Activate
Worksheets.Paste
End If
Next
Next i
End Sub
https://www.mrexcel.com/forum/excel-questions/92140-copy-row-if-cell-contains-specific-word.html
please do advise and thank you for helping me solve this tricky problem
I'm trying to copy some rows,
I have a Worksheet Called "Data". This has all the data.
I have a worksheet called "Copy Rows"
19170
If Column B Cells in worksheet "Data" has a word in it from the list found in Worksheet "Copy Rows"
I would like to copy that row on to Sheet2
I know that you can create an array and then do some auto filtering but it become a bit too much confusing so what I did was I put it in a list, so its easy for me to understand and see my rows that need to be copied.
Now I set it up from all my researching best as I can but I've got stuck on the last bit which is how do I actually copy the Rows now?
Now onto the next sheet.
Sub Copy_Rows()
Dim CopyRowsList As Worksheet
Dim i As Integer
Dim lRow As Long
Dim iCntr As Long
lRow = 400
Set CopyRowsList = Worksheets("Copy Rows") ' << This list has all the rows i would like to copy if Column B has the word in it
For i = 4 To CopyRowsList.Cells(ws.Rows.Count, 4).End(xlUp).Row Step 1
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 2).Value = CopyRowsList.Cells(i, 4).Value Then ' Column B Cell Contains a word from Worksheet "Copy Rows" Column 4 "D"
' Now here is some problems :(
Rows(iCntr).Copy
Worksheets("Sheet2").Activate
Worksheets.Paste
End If
Next
Next i
End Sub
https://www.mrexcel.com/forum/excel-questions/92140-copy-row-if-cell-contains-specific-word.html
please do advise and thank you for helping me solve this tricky problem