Copy & Paste Value based on input box

[mst3kr]

I'm looking for a macro that will copy & paste value a range of cells UP by a certain number of rows based on a value put into an input box.

I have a macro that allows me to copy a row of formulas down based on an input box but I haven't been able to manipulate it to do what I'm after above.

Sub Add_New_Rows_at_Bottom()
Dim LR As Long
Dim j As Variant
Sheets("Sheet1").Select
LR = Cells(Rows.Count, 1).End(xlUp).Row
j = InputBox("Enter number of rows to be added:")
Sheet5.Range("A20:V20").Copy Sheet1.Range("A" & LR + 1).Resize(j)
Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1).Select
End Sub


Any ideas?

 

[rlv01]

What do you mean by "up"? Up relative to what? Can you provide an example?

Lets say that LR is row 10 (LR = 10), and the user inputs 5 rows to be added (j = 5). What would be the destination cells for your copy?

 

[mst3kr]

What do you mean by "up"? Up relative to what? Can you provide an example?

Lets say that LR is row 10 (LR = 10), and the user inputs 5 rows to be added (j = 5). What would be the destination cells for your copy?

I was imagining that if my data range was A10:A50, if I keyed 5 in the input box, the values from A10:A50 would be copied and then pasted as a value staring in row 5. Or, if I typed 7 in the input box, the values would be pasted starting in row 3...

 

[rlv01]

I was imagining that if my data range was A10:A50, if I keyed 5 in the input box, the values from A10:A50 would be copied and then pasted as a value staring in row 5. Or, if I typed 7 in the input box, the values would be pasted starting in row 3...

Perhaps this.

Sub Add_New_Rows_Above()
    Dim LR As Long
    Dim j As Variant
    
    Sheets("Sheet1").Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row
    j = InputBox("Enter number of rows to be added:")

    Select Case j
    Case ""
        Exit Sub
    Case Is >= LR
        MsgBox "Please choose a number less than " & LR
        Exit Sub
    End Select

    Sheet5.Range("A10:A50").Copy Sheet1.Range("A" & LR - j)
End Sub