Insert New Row After Last Row in Worksheet and Copy Format and Formula from Cell Above

Question

I'm looking for a VBA Macro script that will locate the last row in a worksheet and then insert a new row below it, copying only the format and formula from the row above without the text. I've been able to get so far as locating the last row and copying the entire cell above, text included, but have not been able to figure out the last part of not carrying over the text.

I'm wondering if there isn't some way to macro the process of creating the new row at the end of the sheet and then recreating the formula in that row?

Any help is greatly appreciated!

This is what I have so far that works:

Sub New_Formatted_Row_With_Formula

 'Locates Last Cell
  Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Select
 'Inserts Row Below
  Rows(Selection.Row).Insert shift:=xlDown

End Sub

Sixthsense Sixthsense · Accepted Answer · 2016-05-21T07:17:19

Sub New_Formatted_Row_With_Formula()
Dim rActive As Range

Set rActive = ActiveCell

Application.ScreenUpdating = False

With Cells(Rows.Count, "A").End(xlUp)
    .EntireRow.Copy
    With .Offset(1, 0).EntireRow
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlPasteFormulas
        On Error Resume Next
            .SpecialCells(xlCellTypeConstants).ClearContents
        On Error GoTo 0
    End With
End With

rActive.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Insert New Row After Last Row in Worksheet and Copy Format and Formula from Cell Above

1 Answers