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

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

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
                wsResult.Cells(NextFreeRow, 5).PasteSpecial xlPasteAll
            End If
        Next x
    Next i
End Sub

Any help would be greatly appreciated!

share|improve this question
  • 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

2 Answers 2

active oldest votes
up vote 1 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.351698 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO