copy, paste into a 'found' row on another sheet problem......

[harteorama]

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

 

[Don Guillett]

try this idea

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

 

[Don Guillett]

OR
Sub pastetofoundcell()
Cells(3, 3).Copy Columns(2).Find("orange").Offset(2)
end sub

 

[harteorama]

Hi Don,

thanks for the idea - exactly where would i put this in my code... ?

sorry for the daft q!

cheers

P

 

[Jim Cone]

An alternative, working from your existing code...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Sub CopyAlltoSection2_FAVO()
Application.ScreenUpdating = False
Dim RngColA As Range

Dim i As Range
Dim sAdd As String
Dim Dest As Range
With Sheets("Section 2")
Set RngColA = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Cells
End With
For Each i In RngColA
If i.Value = "Favo" Then
i.Offset(2, 0).Insert shift:=xlDown
Set Dest = i.Offset(2, 0)
Exit For
End If
Next i

Sheets("all").Select
Set RngColA = Sheets("all").Range("A1", Sheets("all").Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
If i.Value = "Avon" Then
i.Resize(, 11).Copy Dest
Dest.Offset(1, 0).Insert shift:=xlDown
Set Dest = Dest.Offset(1, 0)
End If
Next i
Application.ScreenUpdating = True
End Sub
'-----------


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...
-snip-

 

[Don Guillett]

That IS the code to copy range(c3) to the cell 2 rows below the cell with
"Avon". How many Favo(s) do you have? I wasnt' sure of what you were trying
to do. A full explanation might be helpful

 

[harteorama]

Hi Don,

sorry... i used the code that you modified for me.. it seens to do what
i want, except, it doesnt paste the copied values.. i can see it
selecting the range (i.e. 2 rows down), but, it thats it..

Full explanation of what im trying to do..

I have a sheet called 'All' in column A i have a variety of names i.e.
Avon, Devon etc...

I want to copy the rows that are equal to Avon and paste them into the
sheet 'section 2', but only after I find the header for the section
that i want to paste to i.e. Avon and insert the copied cells here.

Hope this helps!!

Many thanks for your efforts.....

 

[harteorama]

Hi Don,

in addition to this.. there can be any number of 'FAVO' in my 'all'
sheet

Sorry, i should have said that the FAVO is the code im using for AVON.

thanks again...