Copy Rows - If Column B has the Word Found in a Worksheet List

[dj44]

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"

copy rows.jpg

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-...ific-word.html


please do advise and thank you for helping me solve this tricky problem

[mana]

you can use "advancedfilter"

[dj44]

Hello Mana,
let me investigate this - I need to set up my criteria in a table - i'll post back if i get stuck