Excel – VBA Excel – Insert row below with same format including borders and frames

excelvba

I want to build a macro that inserts a row below the selected cell with the same format. This is the code I have so far:

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
End Sub

The problem is: This code only partially transfers the format. It does use the same background color for the new row, but it does not use the borders/frames for the cells. How can I achieve that?

Best Answer

The easiest option is to make use of the Excel copy/paste.

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub