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

Dear Excel Forum 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

Please Login or Register  to view this content.

Any help would be greatly appreciated!
Best,
Silvano

PHP Code: 

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 


Because between the two copy/paste commands, an input box inserted in between has disrupted everything.

PHP Code: 

Set myReply = Application.InputBox(prompt:="Please any Stage cell: resupply stages will be added ABOVE this cell", Type:=8) 


I don't know what you will enter into the input box, but I suggest entering the row number you want to insert. For example: 5.
The entile code should be:

PHP Code: 

Sub Test()
Dim LastRow As Long, R
LastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
R = Application.InputBox(prompt:="Please any Stage cell: resupply stages will be added ABOVE this cell")
If Not IsNumeric(R) Or R <= 0 Then
    MsgBox "Invalid row number! Try again"
    Exit Sub
End If
Sheets("Sheet2").Range("A2:A" & LastRow).EntireRow.Copy
Sheets("Sheet1").Select
Rows(R & ":" & R).Select
Selection.Insert Shift:=xlDown
End Sub 


Originally Posted by zankzank

If you don't mind me asking: why there is so much of a difference between entering the row number (your code) and asking the user to select a cell (from which the row number could be calculated)?

As I said before "Because between the two copy/paste commands, an input box inserted in between has disrupted everything"

because you code: copy, then set range, then select, then paste.

PHP Code: 

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 


The "Set" range should be move up, then copy, then select:

PHP Code: 

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


Originally Posted by zankzank

and not this code works too!

Does not work?
Anyway, It works for me.

PHP Code: 


Sub Macro1()
Dim LastRow As Long
LastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
Dim myReply As Range
Set myReply = Application.InputBox(prompt:="Please any Stage cell: resupply stages will be added ABOVE this cell", Type:=8)
Sheets("Sheet2").Range("A2:A" & LastRow).EntireRow.Copy
myReply.EntireRow.Select ' do you have this line? should be.
Selection.Insert Shift:=xlDown
End Sub 


Administrative Note:

@zankzank

We would very much like to help you with your query, however you need to include code tags around your code. I have done it for you once today - please do it yourself this time.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.