Insert (paste) multiple rows below a user-selected cell

[zankzank]

Dear MrExcel community,
I am VBA beginner user and I am struggling with the following problem.
Basically, I need to copy all rows (variable number of rows, hence I am using the LastRow function) from Sheet2 and insert them below a user-selected cell (via InputBox) in Sheet1.

I wrote the following but it basically just adds and empty row.
However, if the destination range is fixed, the same macro would work

Sub Macro1()
Dim LastRow As Long
LastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
Dim myReply As Range

Sheets("Sheet2").Range("A2:A" & LastRow).EntireRow.Copy
Set myReply = Application.InputBox(prompt:="Please any Stage cell: resupply stages will be added ABOVE this cell", Type:=8)
myReply.EntireRow.Select

Selection.Insert Shift:=xlDown

End Sub

Any help would be greatly appreciated!
Best,
Silvano

 

[Georgiboy]

Try it as below:

Sub Macro1()
    Dim LastRow As Long
    Dim myReply As Range
    
    LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    
    Set myReply = Application.InputBox(prompt:="Please select any Stage cell: resupply stages will be added BELOW this cell", Type:=8)
    Sheets("Sheet2").Range("A2:A" & LastRow).EntireRow.Copy
    myReply.EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

 

[zankzank]

Thank you so much @Georgiboy : it works like a charm!

 

[Georgiboy]

You're welcome, thanks for the feedback.

You can mark a solution in the thread by clicking the tick ✔ on the right side of the post that answered your question.

 

[zankzank]

Done! Thank you again for your kind and lightning-fast support: this community is awesome!