Copy Row x number of times from specific row and paste,shift down (VBA)

[JJCam]

Hi,

I need to copy a row and insert x number of times below (thus shifting down the below rows).
The x is a value from another cell.

I am struggling with my code as all I managed to succeed is copying the row and pasting x number of times but it overwrites the below row, it doesn't shift down and I can't find the solution.

Here is my code:

Sub copy_paste_range()
Dim r As Range, i As Long, n As Long
Set r = Range("7:7")
If Not IsNumeric(Range("A2").Value) Then Exit Sub
For i = 1 To Range("A2").Value
n = n + r.Rows.Count
r.Copy r.Offset
Next
End Sub

Thanks for your help!

JJCam

 

[MARK858]

Maybe something like

Sub copy_paste_range()
    Dim x As Integer, rng As Range

    If Not IsNumeric(Cells(2, "A").Value) Then Exit Sub
    x = Cells(2, "A").Value

    Set rng = Range("A7:D7")

    rng.Offset(1).Resize(x).Insert xlDown
    rng.Copy rng.Offset(1).Resize(x)

End Sub

Before

Book1
	A	B	C	D
1
2	5
3
4
5
6
7	row	to	copy	from
8
9
10	Notes	and	comments	row
Sheet2

After

Book1
	A	B	C	D
1
2	5
3
4
5
6
7	row	to	copy	from
8	row	to	copy	from
9	row	to	copy	from
10	row	to	copy	from
11	row	to	copy	from
12	row	to	copy	from
13
14
15	Notes	and	comments	row
Sheet2

 

[JJCam]

Hi,
Thanks so much for the quick answer. It works like a charm!!
I will check the documentation to understand better the resize and insert properties.

Thanks again, I really appreciate it. I was really desperate after hours trying to make it work.

 

[MARK858]

You're welcome