copy and paste formula

[jaypatel]

Hi

What would be the code if looking at some particular text in column F, then copy that particular row to "Summary" tab. The text in question is "starter".

eg if cell f2="Starter" then copy row 2, onto summary sheet.
if cell f3="leaver" then do not copy.
if cell f4="starter" then copy row 4 onto summary sheet (and after the first row that was copied)
if cell f5="starter" then copy row 5 onto summary sheet (and after the first 2 rows that was copied).

Thanks

Jay

 

[Firefly2012]

Hi Jay

One way would be to loop thru the range identfying the cells using Find and copy those eg:

Sub CopyRows()
Dim rFound As Range
Dim strFirstAddress As String

Set rFound = Range("F:F").Find(What:="starter", LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)

If Not rFound Is Nothing Then
    strFirstAddress = rFound.Address
    Do
        rFound.EntireRow.Copy
        With Sheets("Summary")
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial
        End With
        Set rFound = Range("F:F").FindNext(rFound)
    Loop Until rFound.Address = strFirstAddress
End If

End Sub