copy last row down an identified (variable) number of rows

[gheyman]

I need code where the user can enter a number (number of Rows) in a cell (B3) and have a button that allows them to copy the last row down the number of times identified in the B3.

If they but 5 in cell B3 the code finds the last row in column B and copies it down 5 rows.

Any help is appreciated.

 

[Joe4]

Try this:

Sub MyCopyMacro()

    Dim lr As Long
    Dim n As Long
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row

'   Find number of rows to copy down
    n = Range("B3")
    
'   Copy down 5 rows
    Range(Cells(lr + 1, "B"), Cells(lr + 5, "B")) = Cells(lr, "B")
    
End Sub

You can add some validation or error handling if you first want to confirm B3 has a number.

 

[gheyman]

Perfect. Thanks Joe

 

[Joe4]

You are welcome.