try this idea

Sub pastetofoundcell()
Cells(1, "a").Copy Cells(Columns(1).Find("Avon").Row + 2, 1)
End Sub

--
Don Guillett
SalesAid Software

wrote in message
ps.com...
Hi all,

can anybody please help...!?!?!?


I have this code, and i am trying to modify it...

I want to change the line 'Set Dest = Sheets("Section
2").Range("A29")', so that it doesnt insert the copied data from range
A29, but will look for a phrase in Col A i.e. "Avon" and then paste the
copied data 2 row beneath this.... any ideas.. ive spent hours and i
simply cannot get this work...

Also, is there a way to paste the copied data into a New Row - as
opposed to simply copying over the row? i.e the Row beneath the copy
will be shifted down.....

:-((

ANY help will be greatly appreciated...

'** this copies all those with FAVO in col A to sheet Section2

Sub CopyAlltoSection2_FAVO()

Application.ScreenUpdating = False

Dim RngColA As Range

Dim i As Range
Dim sAdd As String
Dim Dest As Range

Sheets("all").Select
Range("A1").Select

Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))

Set Dest = Sheets("Section 2").Range("A29")


For Each i In RngColA

If i.Value = "FAVO" Then

i.Resize(, 11).Copy Dest

Set Dest = Dest.Offset(1)
End If

Next i

Application.ScreenUpdating = True

End Sub