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

try this idea

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

OR
Sub pastetofoundcell()
Cells(3, 3).Copy Columns(2).Find("orange").Offset(2)
end sub
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> try this idea
>
> Sub pastetofoundcell()
> Cells(1, "a").Copy Cells(Columns(1).Find("Avon").Row + 2, 1)
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>

Hi Don,

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

sorry for the daft q!

cheers

P

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
'-----------


<(E-Mail Removed)> wrote in message
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-

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Don,
>
> thanks for the idea - exactly where would i put this in my code... ?
>
> sorry for the daft q!
>
> cheers
>
> P
>

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

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