VBA to insert row in named range keeping formatting and formulas

[matthiasarnbert]

Hi. I'm trying to create a button that will insert a new row on the second row of a named range. I would like this new row to keep the same formatting and formulas as the row below it. The below code works well in terms of adding the new row on the second row of the named range. However, what do I need to add to also keep the formulas and formatting from the row below it? Any advice is appreciated!

Private Sub CommandButton1_Click()
Dim oRange As Range
Dim iColumns As Integer
Dim iRow As Integer

'Use Object variables to make code more legible
Set oRange = Worksheets("Sheet1").Range("Meals")
'Get Columns Width of named range
iColumns = oRange.Columns.Count
'Target the second row of the named range
iRow = 2

Set oRange = Range(oRange.Cells(iRow, 1), oRange.Cells(iRow, iColumns))
oRange.Insert Shift:=xlDown

End Sub

 

[bebo021999]

Like this: Copy/paste may keep source format and formula

Private Sub CommandButton1_Click()
Dim r As Range
With Range("Meals").Rows(2)
    .Copy
    .Offset(1, 0).Insert Shift:=xlDown
    .Offset(1, 0).SpecialCells(xlCellTypeConstants).ClearContents ' remove cells without formula. obmit this line if want to keep the hardcode value
End With
End Sub

 

[matthiasarnbert]

Hi. Thanks for your repsonse. The code works great except it inserts the new row below the row that is being copied. Is there a way to get the new row to insert above the row being copied instead? Thanks.

 

[bebo021999]

May be:

.Offset(1, 0).Insert Shift:=xlUp