copy,match,insert row,paste

[ndendrinos]

In sheet2 , column A I have a list of companies
A1=Toyota A2=Ford A3=Chrysler
I keep adding new companies to this column.

In sheet 1 in A1 (through validation I choose an existing company.
I populate row 30 with data

I need a code that will copy the data in row 30 in sheet1
Go to sheet2
Match the chosen company in columnA… travel to the next populated row in columnA
Insert a new row above it then
Offset to column B and paste the data from row 30 in sheet1

This is what the before and after will look like:

Before:

A ............ B ................C ................ D................. E................. F
1 Toyota
2 Ford
3 Chrysler
4

After: (having chosen “Toyota” and populating row 30 in sheet 1 with hello…how…are…you

A ............... B............... C................. D................. E................ F
1 Toyota
..................... hello ............how........... are .................you
2 Ford
3 Chrysler
4

This is the desired result of the code, If you can think of a different approach I would like to hear it.
Many thanks for your help

 

[venkat1926]

not very clear

any how try this macro

Sub test()
Dim r As Range, cfind As Range, co As String, r1 As Range
With Worksheets("sheet1")
Set r = .Range("A30")
co = r.Value
Set r1 = Range(r.Offset(0, 1), r.End(xlToRight))

MsgBox r.Address
With Worksheets("sheet2")
Set cfind = .Cells.Find(what:=co, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.Offset(1, 0).EntireRow.Insert
 r1.Copy

cfind.Offset(1, 1).PasteSpecial
End If
End With
End With
End Sub

 

[ndendrinos]

hello and thank you venkat1926
maybe this will make my question clearer:

In sheet1 Cell A1 I type : Toyota
In sheet1 Cell A30 I type : hello
................in B30 I type : how
................in B30 I type : are
................in B30 I type : you

In sheet2 I have in A1 : Toyota
.......................in A2 : Ford
.......................in A3 : Chrysler

I run the code and in sheet2 I get this:
......A .......... B.............. C................. D................. E................ F
1 Toyota
2............... hello ...........how.............. are ..............you
3 Ford
4Chrysler
5

I run the code a second time & I get this:
......A .......... B.............. C................. D................. E................ F
1 Toyota
2................ hello ...........how.............. are ..............you
3................ hello ...........how.............. are ..............you
4 Ford
5 Chrysler
6

In sheet I cellA1 I now type Ford
In sheet1 Cell A30 I type : very
................in B30 I type : well
................in B30 I type : thank
................in B30 I type : you
I run the code a third time and get this in Sheet2
......A .......... B.............. C................. D................. E................ F
1 Toyota
2................ hello ...........how.............. are ..............you
3................ hello ...........how.............. are ..............you
4 Ford
5................ very ...........well...............thank ............you
6 Chrysler
7

 

[venkat1926]

still some dobts

see this

...............in B30 I type : how
................in B30 I type : are
................in B30 I type : you

you can type only one string in a cell (B30) do you mean to say that you typed
how are you
in B30
or
b30 how
c30 are
d30 you


first you copy sheet 2 data in sheet 3 also (as original data)
then try this macro "test"
if you want to again run this macro you must first run the macro "undo" and then only run "test"
macro "test" will work if either of the alternatives given above is real.

Sub test()
Dim r As Range, co As String, cfind As Range
With Worksheets("sheet1").Columns("A:A")
co = .Range("A1").Value
Set r = Range(.Range("A30"), .Range("A30").End(xlToRight))
With Worksheets("sheet2").Columns("A:A")
Set cfind = .Cells.Find(what:=co, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.Offset(1, 0).EntireRow.Insert
r.Copy
cfind.Offset(1, 0).PasteSpecial
End If
End With
End With
Application.CutCopyMode = False
End Sub

Sub undo()
Worksheets("sheet2").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet2").Range("A1")
Application.CutCopyMode = False
End Sub

 

[ndendrinos]

sorry my bad and you are correct it is :
b30 how
c30 are
d30 you
the code works fine,
Thank you again venkat1926
have a good day