Home Find a specific word in a cell and copy the rows next to it to another worksheet

# Find a specific word in a cell and copy the rows next to it to another worksheet

user6645
1#
user6645 Published in September 20, 2018, 5:24 pm

I would like to copy rows A-E whenever the word FLAG is found in row H and I want to skip all rows where Flag is not found. Below is example of the table I am working with and what I would want my result table to look like.

This is the code I have, however it is not copying the rows A-E that have Flag it is just copying the first three rows to the new sheet.

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Duration")
Dim wsResult As Worksheet: Set wsResult = Sheets("Report")

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 3 To LastRow
For x = 8 To 8
If ws.Cells(i, x) = "FLAG" Then
NextFreeRow = wsResult.Cells(wsResult.Rows.Count, "A").End(xlUp).Row + 1
ws.Range("A" & i & ":C" & i).Copy
wsResult.Range("A" & NextFreeRow).PasteSpecial xlPasteAll
ws.Cells(i, x - 4).Copy
wsResult.Cells(NextFreeRow, 4).PasteSpecial xlPasteAll
ws.Cells(i,x-3).Copy
wsResult.Cells(NextFreeRow, 5).PasteSpecial xlPasteAll
End If
Next x
Next i
End Sub


Any help would be greatly appreciated!

• Has code indentation become uncool? – Jeeped Feb 14 at 15:22
• by "row" do you mean columns? in excel, rows are numbers and columns are letters – Sirmyself Feb 14 at 15:24