VBA/Marco copy paste a range down a sheet

[drop05]

Hello, i was wondering if there is a way to have a range that is created "RNG" and have it copy and pasted above the original range

So i have a range from rows A5:A10 called RNG.
the rows have just

1
2
3
4
5

with the empty row above the 1 being part of the range, I would want that range copied and shifted down with the same format pasted above it, using the range to know what format to get
because i am going to try and create a button to ask a user to select a range and how many times to replicate it
so if chose 1 more to add it would be, or if they added 2 but actually needed more they could do so even after adding some of the range already

1
2
3
4
5

1
2
3
4
5

 

[OaklandJim]

I hope that I understood your need. Sub below seems to do what you want. In the example Rng starts in A5 and includes an empty cell (so six cells).

Sub CopyRangeAboveCount()

    Call CopyRangeAbove(2)

End Sub


Sub CopyRangeAbove(piCount As Long)

    Dim wsDataSheet As Worksheet

    Dim rSourceData As Range

'   Cell where copied data is put.
    Dim rTargetCell As Range
    
'   Count of data rows in the source data range (Rng)
    Dim iDataRangeRowsCount As Long
    
'   First row for source data range (Rng)
    Dim iSourceDataRow1 As Long

'   Count of spaces to leave above each paste
    Dim iSpacesAbove As Long

'   Used for looping through the count of copies sent here in
'   parameter piCount.
    Dim iCopy As Long
    
'   Set sheet object to point to the worksheet used.
    Set wsDataSheet = ThisWorkbook.Worksheets("Sheet1")
    
'   Set range object to point to the source data.
    Set rSourceData = wsDataSheet.Range("Rng")
    
'   First row of the source data at the start of the macro.
    iSourceDataRow1 = rSourceData.Cells(1).Row
    
'   Count of spaces to leave above data inserted.
    iSpacesAbove = 1
    
'   Count of rows in the data range.
    iDataRangeRowsCount = rSourceData.Rows.Count
    
'   Clear values below the range named Rng
    rRangeToClear = rSourceData.Cells(iDataRangeRowsCount + 1).Resize(1000).Clear

    For iCopy = 1 To piCount
    
'       Have to reset target range twice each iteration because it gets
'       pushed down when inserting rows.
        Set rTargetCell = wsDataSheet.Cells(iSourceDataRow1, rSourceData.Column)

'       Insert the necessary rows to prepare for copying the source data to the top.
        rTargetCell.Resize(iDataRangeRowsCount + iSpacesAbove).Insert Shift:=xlDown

        Set rTargetCell = wsDataSheet.Cells(iSourceDataRow1, rSourceData.Column)

        rSourceData.Copy
        rTargetCell.PasteSpecial xlPasteFormats
        rTargetCell.PasteSpecial xlValues
    
    Next iCopy
    
    Application.CutCopyMode = False
    
'   Leave user in the cell just above the first dataset.
    wsDataSheet.Cells(1, rSourceData.Column).End(xlDown).Offset(-1).Select
    
End Sub