Add a Row to an AutoFilter with VBA

Adding a row to the bottom of an AutoFilter is something we do often enough but doing it without code requires more clicks than I would like - copy an existing row, select the row below where the existing bottom row is, then opening up Paste Special. We probably want to paste both Formats and Formulas, so we have do one, reopen Paste Special and do the it again. Okay, things have improved slightly with Excel 2010, but even so...

So, let's use some VBA to help reduce some RSI.

Sub AddRowToFilter()

    On Error Resume Next

    With ActiveSheet.AutoFilter.Range

        .Rows(2).Copy

        With .Offset(.Rows.Count).Rows(1)
            .PasteSpecial xlPasteFormats
            .PasteSpecial xlFormulas
        End With

    End With

    Application.CutCopyMode = False

    If Err <> 0 Then MsgBox "This sheet has no filter"

    On Error GoTo 0

Msgbox "Excel is really fun ;)"


End Sub

Note the message box just in case there is no filter.

Excel is fun :)