vba to insert copied row from sheet2 to sheet1 when condition is met

[vbauser_]

Hi everyone, I tried searching the thread and couldn't find the exact solution to what I am trying to accomplish. I have a workbook with 2 sheets named sheet1, sheet2 in the following format.

Sheet 2 - static table
NAME AGE
Apple 11
Bella 32

Sheet1
NAME AGE ID
Cathy 21 321
David 22 546
Ed 32 900

I would like to copy a row from sheet1, and insert into sheet1 when ID=546. So the final result for Sheet1 would look like:

Sheet1
NAME AGE ID
Cathy 21 321
David 22 546
Bella 32
Ed 32 900


I tried creating an empty row when ID=546 and then copy and paste the row to the last empty cell. But the row ended up being underneath Ed.

Please help!

 

[mumps]

How do you determine which row from Sheet2 to copy? In this case it was "Bella 32". Why not "Apple 11"? Also, do you always want to paste the row below ID 546? Are the NAME, AGE and ID in columns A, B and C?

 

[vbauser_]

How do you determine which row from Sheet2 to copy? In this case it was "Bella 32". Why not "Apple 11"? Also, do you always want to paste the row below ID 546? Are the NAME, AGE and ID in columns A, B and C?

Sheet2 and Sheet1 contains the same data column exception of Sheet2 (It has a unique ID). However data is static in Sheet2, so I am able to refer the row that I want to copy over to Sheet1 by the row number.

But I would like to insert the copied row from Sheet2 only when Sheet1 ID="xxx". This will allow me to update the ID anytime.

Does that make sense?

The closest I've gotten is creating the empty row where Sheet1 ID="xxx" and I couldn't find a way to bring the data from Sheet2 over to Sheet1.

 

[mumps]

I'm sorry but my questions still haven't been answered. Let's assume that in Sheet2 you have 1000 names. How do you decide which of those names to copy? It could be any one of them. Also, when you say "Sheet1 ID="xxx"", how do you decide what the value of "xxx" is? It could be 546, 321, 900 or any other number.

 

[vbauser_]

I'm sorry but my questions still haven't been answered. Let's assume that in Sheet2 you have 1000 names. How do you decide which of those names to copy? It could be any one of them. Also, when you say "Sheet1 ID="xxx"", how do you decide what the value of "xxx" is? It could be 546, 321, 900 or any other number.

Sorry about not giving you direct responses.

For Sheet2, I dictate which of the 1000 names. Because it is a static/legacy table, I can just refer the name that I want to bring over by the row number.

For Sheet1, I also dictate what "xxx" in Sheet1 ID. The rule is that the copied from Sheet1 must be inserted below the ID.

 

[mumps]

Try:

Sub InsertRow()
    Application.ScreenUpdating = False
    Dim rowNum As String
    Dim ID As String
    Dim foundID As Range
    rowNum = InputBox("Enter the row number you wish to copy.")
    If rowNum = "" Then
        MsgBox ("You have not entered a row number.")
        Exit Sub
    End If
    ID = InputBox("Enter the ID number.")
    If ID = "" Then
        MsgBox ("You have not entered an ID.")
        Exit Sub
    End If
    Set foundID = Sheets("Sheet1").Range("C:C").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundID Is Nothing Then
        Sheets("Sheet2").Rows(rowNum).EntireRow.Copy
        Sheets("Sheet1").Rows(foundID.Row + 1).Insert
    End If
    Application.ScreenUpdating = True
End Sub

 

[vbauser_]

I was able to tweak it slightly to do exactly what I need.

Thank you so much for providing the code!

 

[mumps]

You are very welcome.