Given that you haven't over-explained your question here's a solution you may
be able to build on. It's worksheet code and goes in the sheet you want to
copy the data from

Sub stance()
Dim MyRange As Range
Dim CopyRange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each C In MyRange
If UCase(C.Value) = "SOMEVALUE" Then
If CopyRange Is Nothing Then
Set CopyRange = C.EntireRow
Else
Set CopyRange = Union(CopyRange, C.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
lastrow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
CopyRange.Copy Sheets("Sheet2").Range("A" & lastrow + 1)
End If

End Sub

Mike

"TX_AGS" wrote:

I would like to create a macro that copies and pastes data from 1 worksheet
to another, but each time it is run it records the data in a new row. Any
help appreciated.