VBA: Inserting variable number of rows + copy/paste

[Matthew_Schu]

Hello!

I am fairly new to VBA and I'm trying to insert a number of rows equal to the value of a number in a cell -1. Then I would like to copy and paste the information that was on the line prior to inserting the blank rows. So far, I've been able to insert a single blank row but nothing past that. The number I'm referencing is in column "U."
I would greatly appreciate any and all help. Thank you.

Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long

Col = "U"
StartRow = 1
BlankRows = 1

LastRow = Cells(Rows.Count, Col).End(xlUp).Row

Application.ScreenUpdating = False

With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) > 1 Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With

Application.ScreenUpdating = True

End Sub

 

[Matthew_Schu]

I'm not sure how sloppy my answer here is but just to close the loop on this, I edited Mr. Foster's code above to change the copied row to be the one below where the blank rows were being added. Please see below for the code and a sincere thank you to @ChrisFoster.

Option Explicit

Sub InsertRows()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Dim i As Long

Col = "U"
StartRow = 1

LastRow = Cells(Rows.Count, Col).End(xlUp).Row

Application.ScreenUpdating = False

With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) > 1 Then
BlankRows = .Cells(R, Col) - 1
For i = 1 To BlankRows
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
Next i
.Cells(R + i - 1, 1).EntireRow.Copy
.Range(Cells(R, 1), Cells(R + BlankRows, 1)).EntireRow.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next R
End With

Application.ScreenUpdating = True

End Sub