PDA

View Full Version : Copy Rows - If Column B has the Word Found in a Worksheet List



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

mana
05-13-2017, 09:28 PM
you can use "advancedfilter"

dj44
05-14-2017, 08:09 AM
Hello Mana,
let me investigate this - I need to set up my criteria in a table - i'll post back if i get stuck
:yes