Copy data range and insert the copied data by the next row

[TPortsmouth]

Hello,

With the help of Excel expert here, I've created below VBA to copy a user defined range and paste at the bottom.

Sub CopyRecord()
'

lr = Range("A" & Rows.Count).End(xlUp).Row + 1
'lr is to determine the Last Row

Last = Range("A" & Rows.Count).End(xlUp).Row
'This is to to determine the last Row address

Record_Number = Application.InputBox("Which record you want to copy?", Type:=1)
If Record_Number = "" Or Record_Number = 0 Then MsgBox ("Cancelled by user"): Exit Sub
' this checks for Cancel or 0

If Record_Number > Last Then MsgBox "Record does not exist!": Exit Sub
' This is to display error message if user entered a number larger than the existing record number.

Range("A" & Record_Number & ":E" & Record_Number + 2).Copy
    Range("A" & lr).Select
    ActiveSheet.Paste
End Sub

However, I don't know how to change the code into the next row underneath the copied data.

For example, after execute the Macro, Range(4:6) will be copy.

And then using "Insert copied Cells" to insert the whole 3 row's data into the underneath row.

Apart from that, how do I change the code from a specific range to the whole row?

Range("A" & Record_Number & ":E" & Record_Number + 2).Copy

I've tried to changed to something like below and it return error

Range(Record_Number  : Record_Number + 2).Copy

Please offer help, thank you.

 

[sastoka]

Hi There,

Based on your code,

Replace the copy line by this

Rows(Last).Copy Destination:=Rows(lr)

That would copy one row and copy it to the destination

If you want mutliple row you will need to use

Rows("A:" & Last).Copy Destination:=Rows(lr)

Where (A) is the first of the rows you need to copy (Last being the last) and lr the first to copy the data.

I hope it helps.

Best regards,

 

[sericom]

Would your new last row just be lr+2
And to copy the whole row
Range(Record_Number & ":" & Record_Number + 2).Copy

 

[TPortsmouth]

Hi sericom,

Thanks for your advise, yes, the copy whole row works! Thanks.

However, as for the paste destination, I am not asking to paste the data at the last row. I am asking to "Insert" the copied rows right underneath the source row.

Can you further provide your suggestion on this? Thanks.

Would your new last row just be lr+2
And to copy the whole row
Range(Record_Number & ":" & Record_Number + 2).Copy

 

[sericom]

Does this do it

Rows(Record_Number & ":" & Record_Number + 2).Copy
Rows(Record_Number + 3).Insert Shift:=xlDown
Application.CutCopyMode = False

 

[TPortsmouth]

Thank you so much, it's working.

Does this do it

Rows(Record_Number & ":" & Record_Number + 2).Copy
Rows(Record_Number + 3).Insert Shift:=xlDown
Application.CutCopyMode = False